Sumproduct Problem (2003)

Topic

New Reply

I have a formula in the attached sheet that almost works. It looks at what date is entered and counts the occurances and places the total in the appropriate box on the table.

My problem is that if the cells are blank it records the totals under January. If the cells are blank I need it to NOT record at all until a date is entered.

How can this be corrected? Do I need a different formula? thanks.

Reply | Quote

Replies

Howdy. You can add an IF wrapper around it (simplified) (Sorry, after looking at sheet, you have that)

I changed the SUMPRODUCT, and changed the headers in row 3. This is in G4

=IF($F4=””,””,SUMPRODUCT(($C$4:$C$148=$F4)*($D$4:$D$148>=G$3)*($D$4:$D$148<H$3)))

Seems to work

Reply | Quote

=IF($F4=””,””,SUMPRODUCT(($C$4:$C$148=$F4)*(TEXT($D$4:$D$148,”mmmm”)=G$3)))

Yes the formula seems to work except for the January issue. Where would I place the ISNA to correct this???

Reply | Quote

Note, I updated my post after you posted. If possible you can change the headers in row 3, to be the first day of month, then use those as references in your formula.

Then change the formatting to Custom, mmmm, and they will appear as month names.

Reply | Quote

Try

=IF($F4=””,””,SUMPRODUCT(($C$4:$C$148=$F4)*(TEXT($D$4:$D$148,”mmmm”)=G$3)*NOT(ISBLANK($D$4:$D$148))))

Reply | Quote

The NOT ISBLANK was what I needed thanks everyone for your help.

Reply | Quote