How to fix EOMONTH in SUMIFS

[AndreiCCT]

Hello!

I`m trying to workout a formula that adds values from Col A, if two criteria are met.

Crit. 1: Value is between Start Date and End Date.

Crit. 2: Value is from specified Month in fixed cell.

I`m now working with:

Please Login or Register  to view this content.

...but it only works some of the time and it does`t seem to always update to changes in the table.

The table I work with has thousands of entries and quite a lot of subtotals. Also, columns A (values) and column B (Start-Stop Date) have blanks in some cells.

I think that the problem is with the <="&EOMONTH part, because most of the time values seem to be added regardless of the specified month.

Alternative: Is there another formula besides SUMIFS that would be better suited here? I understand SUMIFS has some problems working with 3 columns.

I`m attaching a small table with data from the main. Highlighted area holds the problematic formula.

[PaulM100]

That's because you compare dates with a number(29,30 or 31). It needs to be a date:
=SUMIFS(A:A,C:C,">="&F5,C:C,"<="&DATE(YEAR($F$5),MONTH($F$5),EOMONTH(F5,0)))

[AliGW]

What date are you expecting this to return?

EOMONTH(F5;0)

It returns 30/09/2019.

Anything less than this date (including dates in August) will be included.

[KOKOSEK]

01-08-2019 is less or equal 30-09-2019 so that why you've got difference equal of line with 01-08-2019 in C.
If I am correctly read you needs you want to create criteria for col C not less than end of month but IN month.
Sum all between dates (in B) which have req. month in C (01/09 - 30/09). Am I right?
Maybe try like (I18):

Please Login or Register  to view this content.

[AndreiCCT]

Hi!
Maybe it would be better to explain exactly what this is for:

Col A is Invoice value
Col B is Date invoice arrived
Col C is Invoice Date

What I need from the formula is to SUM values of the invoices from September 2019 [F5] that have arrived between the dates in G18 and G19.
Both F5 and G18:G18 are subject to change.

So what I have until now is just G18:G19 doing OK, but not SUM-ing values from just F5 - September 19.

I hope it`s clearer now.

Thank you!

[AliGW]

No, you need to read the comments made and act upon them!

=SUMIFS(A:A;B:B;">="&G17;B:B;"<="&G18;C:C;"<="&EOMONTH(F5;0))

The section in red in your formula is looking for dates less than or equal to 30/09/2019 - this means it will include dates in August in that column (C).

In other words, the formula is returning what you are asking it to return.

Read the various comments above about how to get round this.

[shukla.ankur281190]

tRY

=SUMIFS(A:A,B:B,">="&G18,B:B,"<="&G19,C:C,">="&EOMONTH(F5,-1)+1,C:C,"<="&EOMONTH(F5,0))

Result must be 297178.56

[AndreiCCT]

Tested using KOKOSEK`s formula and it seams to be working like it should. I got trapped at the &EOMONTH part and didn`t think it through.
Thank you all for your quick responses. Saved me a lot of headache!
Havea great day!

[KOKOSEK]

You welcome. Happy to help.
Thanks for rep.

[AndreiCCT]

Hello again!

I know I`m just being lazy right now ... but:
Is there any way I can combine the two formulas below?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Something like:

Please Login or Register  to view this content.

Your formula works great but I`m trying to get it to work on some other stuff that needs more criteria.
Basically, I need it to do all it does now, but this time take into account another two columns [E and C].
The criteria for this new one is: SUM F:F according to 1st formula but skip rows if CC=107382 and EE <> 21,22,40 or 24.

PS: Second formula was a True / False scenario so I don`t know if it can work with the first one.

Old:

Please Login or Register  to view this content.

Thank you so very much!

[AliGW]

This is a new query - please start a new thread with an appropriate title that refers to joining formulae.

[PaulM100]

The correct result would be 198, not 363, because you have 107382 in column C,row 15. Try:

Please Login or Register  to view this content.

[AndreiCCT]

Hi, AliGW! I din`t think it necessary since it relates to the same formula. Hope it`s not a problem.

PaulM100. If 107382 = 21, 22,40 or 24 the formula should SUM. Only if C:C = 107382 and E:E differs from the 3 values, the formula should skip.

Thanks!

[AliGW]

OK - leave it this time. Bear my advice in mind in future, please.

[PaulM100]

Simplest way wold be:

Please Login or Register  to view this content.

[AndreiCCT]

AliGW - I`ll keep that in mind. Thank you!

PaulM100 - Seams to work but I`ll have to test it on the big table. Thank you very much and I owe you a beer!

[PaulM100]

Here is another possibility:

Please Login or Register  to view this content.

[AndreiCCT]

Hello, PaulM100!
Sorry for the delay but I was AFK over the weekend.
Your 1st formula does the job perfectly, so there is no need to test the second one.
I`m hoping to manage and butcher it to do a COUNT using the same criteria, but for now, all is well.
Thanks again!