- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2006 May
- Re: Total or Count based on multiple conditions

Subject: | Re: Total or Count based on multiple conditions |

Posted by: | Dave Peterson (peters…@verizonXSPAM.net) |

Date: | Wed, 17 May 2006 |

if you're working with dates, you have to enter dates:

=SUM(IF((B2:B657=date(2006,4,20)*(F2:F657="d"),G2:G657))

Personally, I like =sumproduct()'s syntax (and not having to array enter the

formula):

=sumproduct(--(b2:b657=date(2006,4,20),--(f2:f657="d"),(g2:g657))

Adjust the ranges to match--but you can't use whole columns (like your array

formula).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses

to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:

http://mcgimpsey.com/excel/formulae/doubleneg.html

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

--

Dave Peterson

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

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