If Question

  • Thread starter Thread starter Jonathan Zingman
  • Start date Start date
J

Jonathan Zingman

I want to do some conditional sums, averages, etc. So I
used the conditional sum wizard to help set up the
condition. It put into the cell,
{=SUM(IF($AC$2:$AC$177=1,$B$2:$B$177,0))}
complete with {}, and the sum evaluates properly. When I
click in the cell to edit, the {} disappear, and if I just
click the check mark afterwards, the cell no longer
evaluates the same, it evaluates to 0. If I add the
brackets back, then it doesn't evaluate at all. In fact,
I can't seem to get an IF expression to work at all,
except by using the conditional sum wizard, with the
mysterious {}. I'm running Excel 2K on a Win2K system.
Is there a setting I'm missing?

Thanks

Jonathan
 
Hi Jonathan
the {} indicate an array formula. you have to enter these kind of
formulas not by hitting inly the ENTER Button but by hitting
CTRL+SHIFT+ENTER

HTH
Frank
 
Frank:

Thanks for the quick response. I realized that was part
of the answer just after posting, from another of your
posts. So now that I understand that part, I want to get
to the real question. I want to count a subset all of the
elements in one column that have a certain value in
another. I tried:

=COUNTIF(IF($AC$3:$AC$202=1,$B$3:$B$202,0),AF2)
Hoping to count the elements in B that have a 1 in AC and
the value in AF2.
but I get #VALUE! as a response, even when I use
CTRL+SHIFT+ENTER. I figured the range shoould come from
the IF statement, and AF2 should be the criterion. What
am I missing?

I've also tried
=COUNT(IF(AND
($AC$3:$AC$202=1,$B$3:$B$202=AF2),$B$3:$B$202,0))

with no luck.

Thanks
 
Hi Jonathan
COUNTIF (as well as SUMIF only accepts one criteria). You can use
SUMPRODUCT instead:
=SUMPRODUCT(($AC$3:$AC$202=1)*($B$3:$B$202=AF2))
will count all instances with a 1 in column AC and the value AF2 in
column B

If you want to sum a column based on these criteria use:
=SUMPRODUCT(($AC$3:$AC$202=1)*($B$3:$B$202=AF2),$C$3:$C$202)
will sum the entries in column C based on the criteria in column AC AND
column B

for both formulas there is no need to enter them as array formulas
HTH
Frank
 
Jonathan,

Try

=SUMPRODUCT(($AC$3:$AC$202=1)*($B$3:$B$202=AF2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top