Sum of data entries that meet multiple criteria

[DPashley]

Hi,

I am trying to create a formula to add up all entries that match multiple criteria. Currently I am using a "countifs" formula but it doesn't appear to be working (evaluates as 0 when in my test I know it should be 2).

=COUNTIFS(Table1[Order date],MONTH(Table1[Order date])=1,Table1[System type],"UVX")

In my spreadsheet I have a table set up which holds the data. What I am trying to get the formula to do is add up the number of entries that match 2 criteria.

These are:

• If an entry happened in a certain month (this is in table 1 column "Order date")
• If an entries type matches a string (this is in table 1 column "System type")

Thanks for any help!

[AliGW]

Attach the workbook so that we can troubleshoot it.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[AliGW]

This syntax is wrong:

=COUNTIFS(Table1[Order date],MONTH(Table1[Order date])=1,Table1[System type],"UVX")

Should be:

=COUNTIFS(MONTH(Table1[Order date]),1,Table1[System type],"UVX")

[DPashley]

Thanks for the reply!

Using that formula, I get a message that the formula contained an error but I don't see where...

[AliGW]

Try this:

=COUNT(IF(MONTH(Table1[Order date])=1,IF(Table1[System type]="UVX",Table1[Order date])))

[DPashley]

That worked a treat! Thanks!

[sandy666]

maybe try this: =SUMPRODUCT((Table1[System type]="UVX")*(MONTH(Table1[Order date])=1))

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[DPashley]

Now that I have completed my spreadsheet, it seems that if all the data inside the table is removed, the formula evaluates as 1 regardless.

I believe it is something to do with how the month formula works and returns false which equates to 1 for some reason?

Any ideas?

[JeteMc]

See if the following is of any help:
1) Paste the following into cell C2 on the Stats sheet:

=SUMPRODUCT((MONTH(Table1[[Order date]:[Order date]])=ROW(1:1))*(Table1[[System type]:[System type]]=C$1))

2) Drag the fill handle over to cell G2,
3) While C2:G2 is still selected double click the fill handle of cell G2,
4) Paste the following into cell B2 on the Stats sheet: =SUM(C2:G2)
5) Double click the fill handle of cell B2.
Let us know if you have any questions/problems.