Column Combinations

Closed
BecSimmo Posts 3 Registration date Friday November 22, 2013 Status Member Last seen November 24, 2013 - Nov 22, 2013 at 11:14 PM
BecSimmo Posts 3 Registration date Friday November 22, 2013 Status Member Last seen November 24, 2013 - Nov 24, 2013 at 12:39 AM
I have a table that has numerous columns. I need to calculate the total number of units used when the combinations of Cells A and B Appear. For example

Column A Column B Column C
Original Lights Upgraded Light No. of Lights installed
A B 100
A C 50
C F 60
A B 50
D E 125
C F 55

I want in the new worksheet to return something similar to the following:
Column A Column B Column C
Original Lights Upgraded Lights No. of Lights installed
A B 150
A C 50
C F 115
D E 125

I hope that makes sense

4 responses

Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Nov 23, 2013 at 12:49 AM
Hi,

Use the formula:

=SUMIFS(Sheet1!\$C\$2:\$C\$7,Sheet1!\$A\$2:\$A\$7,A2,Sheet1!\$B\$2:\$B\$7,B2)

The sample file is here:

Sample File

Hope this helps.
BecSimmo Posts 3 Registration date Friday November 22, 2013 Status Member Last seen November 24, 2013
Nov 23, 2013 at 08:04 PM
Thanks Zohaib

One other thing, do you know how I can get the type of lights to appear, so that I don't have to type each light type in before calculating the table?
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Nov 24, 2013 at 12:34 AM
Hi BecSimmo,

Do you mean it should appear in the following format?

No. of Lights installed
150 B
50 C
115 F
125 E

If yes, check this formula:

=SUMIFS(Sheet1!\$C\$2:\$C\$7,Sheet1!\$A\$2:\$A\$7,A2,Sheet1!\$B\$2:\$B\$7,B2)&" "&B2

Sample file is here:

Sample File

Hope this helps.
BecSimmo Posts 3 Registration date Friday November 22, 2013 Status Member Last seen November 24, 2013
Nov 24, 2013 at 12:39 AM
Sorry, I am not very good at explaining this.

Column A Column B Column C
Original Lights Upgraded Light No. of Lights installed
A B 100
A C 50
C F 60
A B 50
D E 125
C F 55

I want in the new worksheet to return in Column A the first combination of (returning what the original light was), Column B would return the upgraded light type of the combination, with column C returning the No. of Lights installed in that combination