How to prevent sumproduct from double counting

sjakubik

Board Regular
Joined
Oct 20, 2009
Messages
54
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)))))
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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:

Code:
=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:

Code:
=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))
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,958
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top