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

Subject: | Total or Count based on multiple conditions |

Posted by: | CC (calum31…@btopenworld.com) |

Date: | 17 May 2006 |

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

- RE: Total or Count based on multiple conditions posted by bpeltzer on Wed, 17 May 2006
- Re: Total or Count based on multiple conditions posted by Dave Peterson on Wed, 17 May 2006