Sum if formula

[ashl]

Hi,


I have a silly question - I am using a sum if formula. The values in my sum_range are based on a when formula. When I use my sum if formula it doesn't acknowledge the numbers in my sum_range. When I change the numbers in my sum_range from the formula to the value my sumif formula works. How do i get the sumif formula to work without having to paste the values in my sum_range as values?


Thanks in advance

 

[Hui]

Ashl


Is it possible that some of the cells in your range contain text instead of numbers?

Is Calculation set to Automatic or Manual?, Change it to Automatic if it isn't

That would explain why you are seeing this issue

 

[ashl]

Hi Hui,


Thanks for your quick reply.

Yes in my sum_range I have a when formula stating when there is a "H" give me a 3, when there is a M give me 2 etc etc. My sum if formula is then looking at this range to sum the 3, 3 etc. Hmm is there a work around for this?


The calculation is set to automatic.


Thanks,

 

[kuldeepjainesl]

Hi Ashl,


Just try to multiply the result by 1 which will force excel to treat value as a number.


Regards,

 

[ashl]

Thanks for the tip it works

 

[kuldeepjainesl]

Glad, I could be a assistance.