How to prevent sumproduct from double counting

[sjakubik]

Hi!
I have a SUM/SUMPRODUCT combination formula (below) to figure out the number of oil rigs in column B with the names D20 or D21, which also have revenue in column H and revenue in any of the other columns L, P, T and W. This works great.

If I have a rig with revenue in column H, and also revenue in column P, it will count it once. Perfect! But if I have a rig with revenue in column H, and columns P and T, it's counting it twice (once for H and P, and once for H and T). I only need it to count once. Do I need to do some sort of subtraction deal to prevent it from double counting? Thanks!!!


=SUM(SUMPRODUCT(--('Golden Source'!H2:H628<>0)+('Golden Source'!L2:L628<>0)+('Golden Source'!P2:P628<>0)+('Golden Source'!T2:T628<>0)+('Golden Source'!W2:W628<>0),--('Golden Source'!B2:B628=D20))+((SUMPRODUCT(--('Golden Source'!H2:H628<>0)+('Golden Source'!L2:L628<>0)+('Golden Source'!P2:P628<>0)+('Golden Source'!T2:T628<>0)+('Golden Source'!W2:W628<>0),--('Golden Source'!B2:B628=D21)))))

 

[DonkeyOte]

I'm confused by your narrative -- using your formula if H and P were to both have revenue this will count as 2 you imply however that it's not double counting.

Assuming the criteria is such that H must have revenue and that any one of L,P,T,W must also have revenue then could you not perhas use:

=SUMPRODUCT(--ISNUMBER(MATCH('Golden Source'!B2:B628,D20:D21,0)),--('Golden Source'!H2:H628<>0),--((('Golden Source'!L2:L628<>0)+('Golden Source'!P2:P628<>0)+('Golden Source'!T2:T628<>0)+('Golden Source'!W2:W628<>0))>0))

of course if the entries are always either blank/0 or positive number (ie always numeric) then given totalled H,P,T & W can never equal 0 unless all empty you could simply add them together and validate result, ie:

=SUMPRODUCT(--ISNUMBER(MATCH('Golden Source'!B2:B628,D20:D21,0)),--('Golden Source'!H2:H628<>0),--(('Golden Source'!L2:L628+'Golden Source'!P2:P628+'Golden Source'!T2:T628+'Golden Source'!W2:W628)>0))