Column Combinations

[Closed]
Report
Posts
3
Registration date
Friday November 22, 2013
Status
Member
Last seen
November 24, 2013
-
Posts
3
Registration date
Friday November 22, 2013
Status
Member
Last seen
November 24, 2013
-
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 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
41
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.
Posts
3
Registration date
Friday November 22, 2013
Status
Member
Last seen
November 24, 2013

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?
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
41
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.
Posts
3
Registration date
Friday November 22, 2013
Status
Member
Last seen
November 24, 2013

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