Summing a column

SuzanneH

Hi,

I would like to count all the rows in a column where the value matches a specific cell. I can get this to work using countif however I can't get it to work when the column is variable. For example

Cell A1 = "Yellow", Cell D1 is the sum of all [A1 value] yellows in the column where the first row =30, cell E1 is the sum of all [A1 value] yellows in the column where the first row=40

So far, I have =(MATCH([cell with value 30], [B]1:[C]1)) returning the correct column number for B. But how do I add a count function so that the column name/number that I am counting is variable?

I should get 2 yellow when summing the column where the first cell is 30.

Cell B1 = 30

Cell C1 = 40

B3 = Yellow

B4 = Green

B5 = Yellow

C3 = Blue

C4 = Green

C5 = Yellow

Paul Newcome

You would need to sum the columns then use an INDEX/MATCH to pull the summed value.

I would suggest adding in another row (for this example I will use row 2) that contains the sum of each column. Then you would use something along the lines of...

=INDEX(B2:C2, 1, MATCH(30, B1:B2, 0))