Re: using sumifs to sum based on month, and criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <bob.NGs@xxxxxxxxxxxxx> wrote in message
news:OrNiDoWqIHA.2520@xxxxxxxxxxxxxxxxxxxxxxx
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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:30F4C251-3CE7-4051-A78A-654C624A9705@xxxxxxxxxxxxxxxx
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" <Jonas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:841619D1-0CEA-4551-AD97-FEDA37B8D371@xxxxxxxxxxxxxxxx
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







.

Relevant Pages

  • Re: word frequency counting
    ... the syntax? ... Would I put commas between like so?: ... You need to also enclose the entire range within a pair of ...
    (microsoft.public.excel.misc)
  • Re: Flexibility of English
    ... and finding that it *is* a semicolon. ... semi-colons seems old-fashioned, perhaps Victorian; ... A dash if it was a futuristic ... Those cannot be commas because of the internal commas in each ...
    (rec.arts.sf.composition)
  • Re: using sumifs to sum based on month, and criteria
    ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... Semi-colons and commas are not the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: using sumifs to sum based on month, and criteria
    ... Yes Bob, it works perfectly - thanks a lot! ... Semi-colons and commas are not the ... And semi-colons are not used for commas, they are used as a separator ... You do not use commas, because on the continent, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Why Has Drew Run Away?
    ... what you're doing amounts to a cluster fuck, ... OMH attempted relevance in ADG by fraudulently claiming that his Care ... it isn't about commas and semi-colons. ...
    (alt.2600)