CODEWITHSUNDEEP
excelexcel-2010worksheet-function
D H
D H

Reputation: 3

Sum Column that matches a given criteria in Excel

How would I sum vertically the columns that meet a given criteria?

For example:

A B C D E F G
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7

If criteria = A, then the formula would give me 4 if B, 8 if C, 12. I would like the criteria input to be a reference-able cell.

Thanks for your help!

Upvotes: 0

Views: 52

Answers (2)

Moreno
Moreno

Reputation: 638

Assuming you have a sheet like this: enter image description here

=SUM(INDEX($BF$5:$BI$16,,Match($BC$5,$BF$4:$BI$4,0)))

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

Use INDEX to return the correct array. Use MATCH to return the correct column:

=SUM(INDEX(2:5,0,MATCH(J1,1:1,0)))

![enter image description here

Upvotes: 1

Related Questions