Sumproduct not counting if the formula result is zero

  • Thread starter Thread starter JGGL
  • Start date Start date
J

JGGL

Hello All

I hope you can help me with this:
1. I have 2 dates in different columns and I'm couting the days
between them e.g: b2-a2 = 0 (when both dates are the same)
2. I'm using sumproduct to count the instances when the result of the
formula above is less than 0 days.
And the issue that I'm faceing is when the actual result of the 1st
formula is zero, the sumproduct formula is not couting those zeros
Could you please gelp me with that?

This is the actual sumproduct formula that I put together
=IF($W33="","",SUMPRODUCT(--('QMR Table'!$F$2:$F$1988=$W33)*('QMR
Table'!$BV$2:$BV$1988=$W32),--('QMR Table'!$CX$2:$CX$1988<=$AE29),--
('QMR Table'!$CX$2:$CX$1988<>"")))

the section that I'm concern is the columns of CX
 
It seems like it should work. Have you assured that the the dates are
in fact equal? They could look to be the same, but, maybe the format
is masking some minor differences. The way I troubleshoot this kind
of issue is to temporarily change the range to which the formula
refers to be a more managable number of rows (like about 5); then,
successively calculate the various arguments in the formula (highlight
on the formula bar, making sure the parentheses match, then hit F9).
When you see which rows are returning false or zero when you believe
they should be true (or 1), then you need to go to your source data
and figure out why it is not as you expected. Possibly differences
are masked by formatting, possibly columns are text by look like
values.

Good luck.

Ken
 
Back
Top