RE: Total or Count based on multiple conditions

Giganews Newsgroups
Subject: RE: Total or Count based on multiple conditions
Posted by:  bpeltzer
Date: Wed, 17 May 2006

You can use sumproduct to capture multiple criteria.  For a count:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657=1))
To sum column G when those conditions are met:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657=1),g2:g657)

"CC" wrote:

> Hi,
>
> I have followed the Help guidance to calculate the number of rows that
> contain two criteria, each from a different column but it only returns
> a zero value. The Help Guidance says:
>
> "Count the occurrences of multiple conditions
> In the following formula, whenever Excel finds "Northwind" in the range
> B5:B25, it then checks for the text "Western" in the same row in column
> C (the range C5:C25). Excel then calculates the number of rows that
> contain both.
>
> =SUM(IF(B5:B25="Northwind",IF(C5:C25="Western",1,0)))
>
> This is an array formula and must be entered by pressing
> CTRL+SHIFT+ENTER."
>
> My formula is this:
> =SUM(IF(B2:B657="20/04/2006",IF(F2:F657="d",1,0)))
>
> This is identical to the Help formula yet it only returns a zero value.
>
> Likewise I have followed the instructions to calculatethe total value
> of cells that satisfy those two criteria using the fomula based on the
> Help example which is this:
> "Create a total based on multiple conditions
> Use the following formula to calculate the total value of cells F5:F25,
> where B5:B25 contains "Northwind" and the range C5:C25 contains the
> region name "Western".
>
> =SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))
>
> My formula is this:
> =SUM(IF((B2:B657="20/04/2006")*(F2:F657="d"),G2:G657))
>
> Again my formula is identical to the Help one yet it also only returns
> a zero value.
>
> Can anyone advise why this might be and what I can do to get excel to
> do the calculations I want?
>
> Cheers
>
> CC

Replies

None

In response to

Total or Count based on multiple conditions posted by CC on 17 May 2006