sumproduct help

[drifted78]

hi, i have the following sumproduct formula

=SUMPRODUCT(--ISBLANK(J3:J201)*(A3:A201>0)*--(A3:A201<(TODAY()-0))*--(A3:A201>=(TODAY()-10)))

it records claims input onto my data base and gives me live totals where there is no end date. my problem is is does not include anything i have input today - that will only be counted from tomorrow, can anyone please tell me how to ammened this?

thanks in advance

 

[joeu2004]

i have the following sumproduct formula

=SUMPRODUCT(--ISBLANK(J3:J201)*(A3:A201>0)*--(A3:A201<(TODAY()-0))*--(A3:A201>=(TODAY()-10)))

it records claims input onto my data base and gives me live totals where there is no end date. my problem is is does not include anything i have input today - that will only be counted from tomorrow, can anyone please tell me how to ammened this?

=SUMPRODUCT(--ISBLANK(J3:J201)*(A3:A201>0)*(A3:A201<=TODAY())*(A3:A201>=TODAY()-10))

The key change is highlighted in red. Other changes are just clean-up.

Note: Be sure you really want ISBLANK. Alternatively, replace --ISBLANK(J3:J201) with (J3:J201=""). The difference is: ISBLANK is true only when the cell is empty -- no constant and no formula. In contrast, J3:J201="" is also true when the cell value is the null string, typically a formula that returns the null string under some conditions.

 

[drifted78]

thank you so much!

 

[Aladin Akyurek]

No need to mix up -- and *, two different coercers...

Either...

=SUMPRODUCT(ISBLANK(J3:J201)*(A3:A201>0)*(A3:A201 <= TODAY())*(A3:A201 >= TODAY()-10))

Or...

=SUMPRODUCT(--ISBLANK(J3:J201),--(A3:A201>0),--(A3:A201 <= TODAY()),--(A3:A201 >= TODAY()-10))

Or...

=COUNTIFS(J2:J201,"",A3:A201,">="&TODAY()-10,A3:A201,"<="&TODAY())