# SUMIFS with matching Month

1. ## SUMIFS with matching Month

Hello and good day,
I am having much difficulty getting this to work, I did some digging around and perhaps it's not the right function or even possible, but I can't quite find the right direction.

I have a table of transactions where column B will be dates such as "2017-09-01" and column H will be a transaction type. I would like to sum column F based on transaction type and date. Here are a few of the examples I have tried. The only way I can get it to sum is if I explicitly match the exact date. Example table below for clarity.

 B H F 2017-09-01 Material 159.49 2017-09-15 Material 2045.59 2017-10-15 Labor 956.58

=SUMIFS('2017'!F1:F100, '2017'!B1:B100, "September", '2017'!H1:H100, "Material") - Sums to 0
=SUMIFS('2017'!F1:F100, MONTH('2017'!B1:B100), "September", '2017'!H1:H100, "Material") - Results in error
=SUMIFS('2017'!F1:F100, MONTH('2017'!B1:B100), 09, '2017'!H1:H100, "Material") - Results in error
=SUMIFS('2017'!F1:F100, '2017'!B1:B100, "*Sep*", '2017'!H1:H100, "Material") - Even a wildcard results in error

I am not sure what else to do... any advice very much appreciated.

2. ## Re: SUMIFS with matching Month

Hi Vidus,

If the first formula works, change to =SUMIFS('2017'!F:F,'2017'!B:B,"2017-09-01",'2017'!H:H,"Material")

Other than that suggestion, maybe you could provide a sample sheet to work with.

3. ## Re: SUMIFS with matching Month

The sample table is pretty much exactly the data I am working with.
The problem with the first formula is that I was just using it as a test, I am looking to sum all values in a particular month, not a specific date. Perhaps I should have left that example out.

4. ## Re: SUMIFS with matching Month

Sorry my brain wasn't engaged.

Try this...

``Please Login or Register  to view this content.``
The answer I get for you small sample data is 2205.08

You can't use the month calculation inside of the Sumifs and you shouldn't use the entire range in a Sumproduct

5. ## Re: SUMIFS with matching Month

I just get #VALUE! from this formula. It appears very complex so I am not quite sure what to look for.

6. ## Re: SUMIFS with matching Month

Can you post a sample worksheet?

7. ## Re: SUMIFS with matching Month

This is very strange, I created a sample workbook using actual data, and the formula did not function until I manually entered another line item... (in red). If you take that away the formula results in #VALUE!

I checked the formatting, seems ok...

8. ## Re: SUMIFS with matching Month

It's because when you empty F8 and H8, the ranges become unequal. By your sample you led me to believe every cell was filled with a value.

Try...

``Please Login or Register  to view this content.``
...Which uses column B to establish the length of the column. To test, delete F8 and H8 and now you get 4323.45

9. ## Re: SUMIFS with matching Month

This seems to work! I'll keep playing with it to apply it to the entire sheet. I wish I knew what was going on with this formula, I like to understand it but I can't figure out what is going on with the 9.9999 etc.
Thank you so much!

10. ## Re: SUMIFS with matching Month

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1