Problem with SUMPRODUCT formula

[Sean15]

Hi:

The formula below gives all required values:
=SUMPRODUCT(--(PostingDate>=$I$2),--(PostingDate<=$K$2),--(TransactionCategory=C36),Amount)

where:
I2 = 9/15/23
K2 = 01/05/24

I adapted formula to return values up to end of month (K2)
=SUMPRODUCT(--(PostingDate>=$I$2),--(PostingDate<=EOMONTH($K$2,0),--(TransactionCategory=C36),Amount)

but Excel returned invalid formula. Could you advise a fix please?

Regards,
S

 

[Alex Blakenburg]

You are missing a 2nd bracket after the EOMonth function .
Also if you swap the separating commas for multiplication you don't need all the double unaries ("--")

=SUMPRODUCT((PostingDate>=$I$2)*(PostingDate<=EOMONTH($K$2,0))*(TransactionCategory=C36)*Amount)

 

[Sean15]

Thank you.
I added missing bracket. Formula with double unaries ("--") returned required value.
I also tested formula without double unaries but Excel returned #VALUE.



The required value is -1,186.70. It's unclear why #VALUE appears in formula evaluation.

Regards,
S

 

[Alex Blakenburg]

Glad to hear your formula is working for you now.
In relation to using multiply, you would need to copy paste in here the exact formula you tried. I can't replicate the error you are getting. Do you definitely have $K$2 in the modified formula with the valid date in the cell ?

 

[Sean15]

Hi:
Formula tried:



Tested value in I2 and K2 for date using =Value()
Value(I2)=45184
Value(K2)=45296

 

[Alex Blakenburg]

That looks fine to me. If the double unary works then that should work.
If you can give me some data that it fails on via XL2BB or via a link to a shared workbook I can have a look from my end.
The most likely to produce a Value error is the EOMonth function, so as a proof on concept you could remove that function just to see if we can the "*" to work in principle.