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
--
tsony


"tsony" wrote:

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

Try this : =SUMIF(C2:G2,"="&A20,C5:G5)
HTH
John
"tsony" wrote in message
...
There is small correction in the question the range is from c2 to k2 and
c5
to k5
--
tsony


"tsony" wrote:

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

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)
--
David Biddulph

"tsony" wrote in message
...
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

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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)
--
David Biddulph

"tsony" wrote in message
...
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

Thanks John.

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

--
tsony


"John" wrote:

Try this : =SUMIF(C2:G2,"="&A20,C5:G5)
HTH
John
"tsony" wrote in message
...
There is small correction in the question the range is from c2 to k2 and
c5
to k5
--
tsony


"tsony" wrote:

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

Hi again
You could use this one also.=SUMPRODUCT((C2:K2=A4)*C5:K5)
HTH
John
"John" wrote in message
...
Try this : =SUMIF(C2:G2,"="&A20,C5:G5)
HTH
John
"tsony" wrote in message
...
There is small correction in the question the range is from c2 to k2 and
c5
to k5
--
tsony


"tsony" wrote:

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

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 InsertFunction and with the
Function Argument menu and can see if it's good and can modify it on that
menu.
HTH
John
"tsony" wrote in message
...
Thanks John.

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

--
tsony


"John" wrote:

Try this : =SUMIF(C2:G2,"="&A20,C5:G5)
HTH
John
"tsony" wrote in message
...
There is small correction in the question the range is from c2 to k2
and
c5
to k5
--
tsony


"tsony" wrote:

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