software

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:

quot;Count the occurrences of multiple conditions
In the following formula, whenever Excel finds quot;Northwindquot; in the range
B5:B25, it then checks for the text quot;Westernquot; 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=quot;Northwindquot;,IF(C5:C25=quot;Westernquot;,1,0 )))

This is an array formula and must be entered by pressing
CTRL SHIFT ENTER.quot;

My formula is this:
=SUM(IF(B2:B657=quot;20/04/2006quot;,IF(F2:F657=quot;dquot;,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:
quot;Create a total based on multiple conditions
Use the following formula to calculate the total value of cells F5:F25,
where B5:B25 contains quot;Northwindquot; and the range C5:C25 contains the
region name quot;Westernquot;.

=SUM(IF((B5:B25=quot;Northwindquot;)*(C5:C25=quot;Westernquot;),F5 :F25))

My formula is this:
=SUM(IF((B2:B657=quot;20/04/2006quot;)*(F2:F657=quot;dquot;),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

CCYou 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)

quot;CCquot; wrote:

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

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

=SUM(IF((B2:B657=date(2006,4,20)*(F2:F657=quot;dquot;),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=quot;dquot;),(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 he
www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
mcgimpsey.com/excel/formulae/doubleneg.html

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

--

Dave Peterson

Hi,

I did the sumproduct thing and it works like a dream. Thanks for that
guys.

CCHi again,

I now need to sum and count from more than one date (e.g. the
cumulative total of houses for sale on 20/04/2006 and 27/04/2006 and
the total value of them). I have tried simply adding the second date to
the sumproduct formulae above using *, OR, but that didn't work. Any
suggestions?

CC