Sumproduct

[anikeh]

HI All

I have to understand what this formula does so I can turn it into some SQL query.
But I am not sure how SUMPRODUCT works
I googled it and tried what i understand but the value i am getting from SQL and the value the formula is returning on Excel are not matching.
Can you explain how this sumporoduct thing works to me?

Please Login or Register  to view this content.

Thanks a lot

[Jdevil]

Hi,

It counts the total of rows for which all conditions are met.

The conditions are the following:
A = "Auto"
C = " "
F = $C28
H = NEWFOUNDLAND or NOVA SCOTIA or NEW BRUNSWICK or PRINCE EDWARD ISLAND
O = table!$M$5
(O <= table!$M$4 AND Q >= table!$M$5) OR (O > table!$M$4 AND Q <= table!$M$5 AND Q >= table!$M$5)

The way it works is that it sums all the cases where it gives one. If all requirements are met, it sums occurences such as 1*1*1*(1+0+0+0)*1*(1+0) = 1, for example