Sum On Conditions with Excel 2007

[tinumathews]

Hi,


I have a range say A1:D10, All of the cells in this range contain the number 8, except for three cells, which contain 5, 2 and 6. The cells are A2, B2 and C2.


I wanted to write a formula in "A11" or any other cell which would Sum only the cells that contain numbers other than 8,


I am able to sum the number 8 in this range in two ways

=SUMIFS(A1:D10,A1:D10,8)and

=SUMIF(A1:D10,8,A1:D10) and both gives me the sum of 296. Now i should be able to sum the ones which is not 8 in this range. What combination should i use.

Or is that its not possible. I dont have a clear idea.


Requesting your help,


Thanks and Regards,


Tinu Mathews

 

[fred]

why don't you do a sum(all) and subtract sumif(range,"8",range)?


Also when you said the number contains "8". do you count 18, 28, 38, etc? or just 8?

 

[Luke M]

=SUMIF(A1:D10,"<>"&8)

 

[tinumathews]

Hi Fred, its only 8 in the entire range. Sorry for not being specific. Well this is just my second post, so, not so well with it.


Hi Luke, Thanks for this you saved my day. I was actually suming up the work hours for the entire team and wanted to sumup the time which was less than 8 hours.


Thanks once again Luke.

 

[Luke M]

You're very welcome. Note that if you truly just want "less than 8" you can use:

=SUMIF(A1:D10,"<"&8)

 

[tinumathews]

Yes luke thats exactly what I want. Now its better.. . Thank you so much for your help.