Conditional Sum - variable criteria

[rainyday]

I wish to sum the values in a column that meets certain critieria such as
greated than 35%. I can write the formula so it works but I have been unable
to enter an absolute cell reference or range name in the criteria of the
formula in order to be able to vary the percentage criteria to be added.

Formula =sumif(percentage_hrs, ">=35%", K15:K202)

I have had to copy the conditional sum formula across multiple columns but
then have to recopy it with the new percentage criteria to try different
results. In contrast, I have been able to use DSum with a range named
criteria which allows me to change the criteria from say 35% to 25% and resum
the values. However, I had to manually enter the column number for each
different column (60 Columns!) fr the sheet to work properly.

Any suggestions to overcome either conditional sum or DSum?

 

[Mike H]

Hi,

Like this, where a1 holds the criteria percentage.

=SUMIF(Percentage_Hrs, ">="&A1, K15:K202)

Mike

 

[Francis]

Hi

The only way is to use a cell to hold the reference

example :

=sumif(percentage_hrs, ">="&B1, K15:K202)

This assume that B1 is where the percentage will be input.

--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[rainyday]

Thanks, and by adding an absolute reference it works ok across multiple
columns.

 

[rainyday]

Thanks, and by adding an absolute reference it works ok across multiple
columns.

 

[rainyday]

Hi

The only way is to use a cell to hold the reference

example :

=sumif(percentage_hrs, ">="&B1, K15:K202)

This assume that B1 is where the percentage will be input.

--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis