Can't figure out why my SUMIFS formula is not working!

[mattsonberg]

I have no idea why this formula is not cooperating. Someone already helped me with this once and it was working, but now I've had to repopulate the data and it's no longer returning any results. Can someone take a look and see what I'm missing here?!Sample Test.xlsx

[Yogi52o]

Your "helper" column on Extracted Data (column H) still has individual dates in it, despite the formatting, while you are trying to use a month-year combo in your SUMIF criteria.

To get around this, you could change the formula in Extracted Data, Column H to:

=TEXT(G2,"mmm-yy")

[6StringJazzer]

You are using the TEXT function to produce a string from the date in row 1 (which is always the first day of the month) to compare to a date in column H (which can be any day of the month). You are therefore comparing a month to a full date, which will match only when the transaction is on the first day of the month. I have modified your formula to compare a date to a date, and I have modified "Extracted Data" so that column H shows the first day of the month for that transaction. Now they will match.

You must maintain that formula in column H.

I think you may have some confusion about using TEXT to compare a string to a date. You are using TEXT to create a string with the same format as the display for the date, but the way a date is displayed has nothing to do with how you compare things to it. The underlying value is the same no matter what the format is. When you do date comparisons, you want to compare to the underlying value, not how the date is displayed.

[Pete_UK]

Your dates in column H of the 'Extracted Data' sheet are proper dates, just formatted to show the month and year. Consequently, you should change your formula in B2 of the 'JSOH By Month' sheet to this:

=SUMIFS('Extracted Data'!$R:$R,'Extracted Data'!$F:$F,$A2,'Extracted Data'!$H:$H,B$1)

then you can copy this across and down.

Hope this helps.

Pete

[6StringJazzer]

Yogi52o has taken a slightly different approach from mine that will work just as well, if you prefer it. Mine results in a simpler SUMIFS formula. Pretty much a matter of preference.

[boopathiraja]

Hi, I gues you want to achieve the summary based on month if so
Try the below formula

Please Login or Register  to view this content.

Does that help ?

[mattsonberg]

That was it! I knew it had to be something simple that I just wasn't getting.