Thread: Summing the data from two sets?

Hi all,

I’m just wondering if anyone can offer any assistance out there, with a query I have regarding my excel predicament.

I’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).

In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.

Is there a simple way/formula of doing this? The idea is that I can then expand it onto three and four parameters per participant. Many thanks for taking the time to read this, and I would really appreciate any guidance!

Mike

Does this work?

=SUMPRODUCT((B2:B16>5)+(C2:C16>10),--(D2:D16>20))-SUMPRODUCT(--(B2:B16>5),--(C2:C16>10),--(D2:D16>20))

Hi makey
possibly
=SUMPRODUCT(--($B$2:$B$16>5)*--($C$2:$C$16>10)*--(D$2:D$16>20))

Originally Posted by NBVC

Does this work?

=SUMPRODUCT((B2:B16>5)+(C2:C16>10),--(D2:D16>20))-SUMPRODUCT(--(B2:B16>5),--(C2:C16>10),--(D2:D16>20))

Hi,

Thanks for both the quick replys,

NVBC, you one seems to work quite well!
Just a quick question, if I want to expand that to other columns i.e. if i insert ones into D how would I adapt that to work?

Assuming that D was >5


Thanks again

Mike

Test this one out:

Code:

=SUMPRODUCT((B2:B16>5)+(C2:C16>10)+(D2:D16>10),--(E2:E16>20))-SUMPRODUCT(--(B2:B16>5),--(C2:C16>10),--(E2:E16>20))-SUMPRODUCT(--(B2:B16>5),--(D2:D16>10),--(E2:E16>20))-SUMPRODUCT(--(C2:C16>10),--(D2:D16>10),--(E2:E16>20))

NBVC, for the first one:

Code:

=SUMPRODUCT((B2:B16>5)+(C2:C16>10),--(D2:D16>20))-SUMPRODUCT(--(B2:B16>5),--(C2:C16>10),--(D2:D16>20))

couldn't you use:

Code:

=SUMPRODUCT(--(((B2:B16>5)+(C2:C16>10))>0),--(D2:D16>20))

or am I missing something ?

If the above assumption is correct ...

I suspect you could push into an OR like the above (ie test SIGN of OR output and use that as multiplier) to the next example though I confess I don't follow 100% - should the result be 8 ?

Code:

=SUMPRODUCT(--(((B2:B16>=5)+(C2:C16>=10)+(D2:D16>=5))>0),--(E2:E16>=20))

(note change of > operators to >= so as to get the 8 output per highlighted rows in sample file...)

apologies if I'm barking up the wrong tree... apologies also for late edits

The first formula I suggested would give 6 per first sample file.

The second formula would generate 8 based on 2nd sample file, if expected result was 6 for that file also you need simply revert the >= operators back to > ... the file was a tad confusing.

You are welcome Makey. Glad it worked...

DonkeyOte, as you probably know by now, I tend to be more brute force (probably why I am not that great in VBA programming)... so my formulas can sometimes be longer than other solutions that might also work (and possibly be more efficient)... I don't have speed calculators to see which is faster... as you know, shorter is not always more efficient, although, in this case, I have a funny feeling yours may be more efficient...

... but for what Makey seems to be doing, it doesn't seem that he/she will see any real performance issues....

... if you have alternative suggestions, please be my guest and provide them... somebody out there could find them useful.

Originally Posted by DonkeyOte

The first formula I suggested would give 6 per first sample file.

The second formula would generate 8 based on 2nd sample file, if expected result was 6 for that file also you need simply revert the >= operators back to > ... the file was a tad confusing.

Using my formula I got 6 in the sample file...

Now we are just walking over each other.....

I was simply trying to illustrate how to avoid double counting in one singular SUMPRODUCT function by virtue of testing the SIGN of the output of the OR's.

Using a much simplified version to illustrate:

Code:

A1: 7
A2: 8

B1: 7
B2: 12

C1: 100
C2: 200

If we want to SUM contents of C where either A > 5 or B > 10 then we know that:

Code:

=SUMPRODUCT((A1:A2>5)+(B1:B2>10),C1:C2)

will generate incorrect result of 500 given we end up with

Code:

=SUMPRODUCT({1+0,1+1},{100,200})

ie we're double counting row 2 given both OR conditions return TRUE


We can avoid the double count in one SUMPRODUCT by simply validating the SIGN of the OR output:

Code:

=SUMPRODUCT(--(((A1:A2>5)+(B1:B2>10))>0),C1:C2)

given this equates to

Code:

=SUMPRODUCT({1,1},{100,200})

ie the summed OR outputs of 1 and 2 are both greater than 0 - thus 1 multiplier is used in each case.

It follows that we can add as many OR conditions as we want prior to the >0 test without affecting result.

(you could use SIGN rather than --(...>0) but Aladin has argued in the past that that approach would be a little slower given the additional function call)