Sumproduct not counting if the formula result is zero

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
 
K

Ken

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top