# Replacing “Sumifs” for adding a value in given range

If the requirement is:- Adding the values in column B by checking the range in column A.

For Example, if the given range is from 100-200, then the values in cell B1,B2,B5,B6 should be added.

In dashboards, we are unable to use Sumifs so there is an alternative way for similar requirements.

What we can do here is, we can write a formula for a range lets say 100-200, so we will add the values which are greater than equal to 100 and from that we can subtract the values which are greater than equal to 200, so we will get the value which is falling between the range 100-200.

the formula is:- SUMIF(A1:B10,”>=100″,B1:B10)-SUMIF(A1:B10,”>=200″,B1:B10),

similarly SUMIF(A1:B10,”>=200″,B1:B10)-SUMIF(A1:B10,”>=300″,B1:B10) &

SUMIF(A1:B10,”>=300″,B1:B10)-SUMIF(A1:B10,”>=400″,B1:B10)

Below is the excel example for better understanding.

Hope this can help !

Thanks,

Yash Patel.

Please comment if any doubt !

Thanks,

Yash

Really good one and helpful,keep on posting this kind of posts

Nice Presentation Keep up the Good Work!!

Nice alternative.

Please check the third one SUMIF(A1:B10,">=300",B1:B10)-SUMIF(A1:B10,">=400",B1:B10) and explain it.

nice document.

Thank you kalpana, I would like to explain you,

In third one, what we are doing is, summing up the values which have their corresponding measures falling in range greater than equal to 300 & subtracting the values which have their corresponding measures greater than equal to 400, but in my example i am not having any values greater than 400, so might be you got confused.

In short the formula does:-

SUMIF(A1:B10,">=300",B1:B10) = 190

&

SUMIF(A1:B10,">=400",B1:B10) = 0

so 190-0=190.

Hope you understood, kindly ask if any doubt, thanks for the like.

Regards,

Yash