# sumproduct help

#### drifted78

##### Board Regular
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?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
i have the following sumproduct formula
Rich (BB code):
``=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?
Rich (BB code):
``=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.

Last edited:
thank you so much!

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())

Replies
5
Views
412
Replies
0
Views
294
Replies
3
Views
236
Replies
13
Views
3K
Replies
4
Views
3K

1,203,530
Messages
6,055,935
Members
444,837
Latest member
TheBams

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back