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))

Eric M Oliveira

Hi Suzanne,

Happy to help, from the description provided it sounds like you would like to count the values that are "Yellow" in a column only when the first cell of that column is equal to the value 30. If yes, you may be able to achieve this utilizing an IF function in combination with a COUNTIF function. Similar to the formula below.

=IF([Column with 30]1 = 30, COUNTIF([Column with 30]:[Column with 30], "Yellow")

Note: Currently we don’t have a method to reference column header names but this will be considered as a possibility for future development. 

Also, from the description provided it sounds like the sheet is structured in a way that may present difficulties in the future when utilizing formulas. You may want to consider separating values that represent different processes into different columns. 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

SuzanneH

Thank you, This is a big help.