# How to prevent sumproduct from double counting

#### sjakubik

##### Board Regular
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
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:

Replies
0
Views
306
Replies
5
Views
457
Replies
7
Views
241
Replies
1
Views
1K
Replies
0
Views
226

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.

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.

### Which adblocker are you using?

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

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