SUM funtion for cells in row containing both string AND with specified month

[Chairman_Wu]

First of all, Greetings!

To try and explain simply as possible i'm trying to accomplish this :

SUM C2:C38 IF B2:B38 = groceries AND IF DATE in A2:A38 is between 1/1/2019-1/31/2019 (any day in the month of january)


Per row, Column A is date, Column B is for what was bought, Column C is how much.

So IF on row 1, $10 was spent ,during January, I would like to add that $10 to the total in a specific cell.



Currently I am just specifically naming the cell ranges that are in January like so:

=SUMIF(B2:B8,"groceries",C2:C8)


After a bunch of googling tried this :

=SUMIFS(C2:C38,B2:B38,"groceries",a2:a38,”>=01-Jan-2017″,A2:A38,”<=31-Jan-2019″)


Didn't work.



Any help is greatly appreciated, sorry if this was answered already but I just haven't been able to find this answer on my own.

[Pete_UK]

What does "Didn't work" mean? Do you get an error message, or a value of zero, or some other value?

Perhaps your dates are not proper dates, but are text values which look like a date.

Maybe the values in column C are text values, rather than real numbers.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as this doesn't work on this forum.

Hope this helps.

Pete

[Chairman_Wu]

Hey Pete, Thanks for the reply.

There was a formula parse error.

I have column A formatted to MM/DD/YYYY, column B Plain Text, and column C to local currency.

I tried changing

=SUMIFS(C2:C38,B2:B38,"groceries",a2:a38,”>=01-Jan-2017″,A2:A38,”<=31-Jan-2019″)

to

=SUMIFS(C2:C38,B2:B38,"groceries",a2:a38,”>=1/1/2019″,A2:A38,”<=1/31/2019″)

(also tried without the quotes)

with no luck.

I've attached the sheet to this reply.



Thanks,
Steve.

[FlameRetired]

Edited: corrected mistake in last formula.

In the attached I removed the month names and ":" so they will match Descriptions. The first of the month (and it can be any day in the target month) is in column E. Then this formula in G1 filled down to G2

Please Login or Register  to view this content.

In G3

Please Login or Register  to view this content.

Copy G1:G3 and paste into G8.