using sumifs to sum based on month, and criteria
Jonas did. I misposted. Simply that. And you knew it.
Tyro
"Peo Sjoblom" wrote in message
...
Where did Bob use month without arguments?
--
Regards,
Peo Sjoblom
"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?
Tyro
"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)
Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")
However, that may not yield the results you are after.
Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month()
function.
Regards,
Ryan---
--
RyGuy
"Tyro" wrote:
MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?
Tyro
"Jonas" wrote in message
...
Hi.
I have a table of entries, structured in the following way:
Column A contains dates. Column B contains a data validated list.
Column D
contains values.
I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.
I tried the following formula to sum over all dates in April,
without
success:
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")
I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.
I would greatly appreciate any help!
/Jonas
|