sumif

[tsony]

Hi

I have a row c2: c25 which contains date row c5:c25 contains amounts. I need
to add the amounts when I select a date from the section box at a20(the date
is selected from the c2:c25 date validation list). The result will be on the
row c27. I tried the formula =sumif(c5:c25, ">=a20", c2:c25) but it adds all
the amounts ignoring the critera >=a20. Is there any easy way to do this?
Some times it shows zero.

 

[tsony]

There is small correction in the question the range is from c2 to k2 and c5
to k5

 

[John]

Try this : =SUMIF(C2:G2,">="&A20,C5:G5)
HTH
John

There is small correction in the question the range is from c2 to k2 and
c5
to k5

 

[David Biddulph]

You need to be very careful if the size of the array you are testing is
different from the size of the array you are counting. You started one
array at C5 and the other at C2. Look at the Excel help for SUMIF to see
how this would be treated.
If the two arrays are the same, you can omit the second occurrence from the
formula.
I'm surprised that you are getting an answer which adds all the amounts and
ignores the >=A20 criterion, as I would expect the syntax of your formula to
be looking for the text string ">=a20" and thus return a zero.

Perhaps try:
=SUMIF(C5:C25, ">="&A20)

 

[John]

Hi David
Normally to have the exact same array, we would be working with columns, but
with rows it's impossible to have the same number but the same range.
I got the syntax from Fred Smith on an old Post and kept it.
You're the Expert but i tested it with few dates and it works.
Your comments are always appreciated, that's how i get better.
Best Regards
John

 

[tsony]

Thanks John.

I got the answer when I put the & symbol before the cell ref. Do you know
why we put the & symbol?

 

[John]

Hi again
You could use this one also.=SUMPRODUCT((C2:K2>=A4)*C5:K5)
HTH
John

 

[John]

HI Tsony
I'm not the expert,David would be the guy to answer that better then me.
But my conclusion is because ">=" is inclosed in brackets excel need to
have this symbal &, it's like saying bigger or equal and A20.
Maybe someone will give you a better answer.
For you to know when i build a formula i go to >Insert>Function and with the
Function Argument menu and can see if it's good and can modify it on that
menu.
HTH
John