Conditional Sum - variable criteria


R

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?
 
Ad

Advertisements

M

Mike H

Hi,

Like this, where a1 holds the criteria percentage.

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

Mike
 
F

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
 
R

rainyday

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

rainyday

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

Advertisements

R

rainyday

Francis said:
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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top