SUMIF: How to add some cells and ignore other cells in a row

[Danish Ayub]

B3=1 C3=100 D3=1% E3=100 F3=10% G3=90% H3=99
(it starts from B3 and ends at AM3 i.e 21 cells wide)

from the said range i wish to SUM only the values which are not percentage
values.
SUM from B3 to AM3 ignoring percentage cells.

i used =SUMIF(B3:AL3,"<100%")-1 to add only the cells having value in
Percentage

 

[Totti]

Percentage in Excel is a number, as all other numbers, so how are you
going to distinguish between 1 and 100%? unless there is no
possibility to have a value of 100% in a cell, and if you can use an
Auxiliary column, you can say in an Aux column(say B4):
=IF(B3<1=FALSE,B3,"") and copy it through under your data, and in the
end, =sum(B3:Bx)

This will cause problems if you have decimal values in your cells like
0.5 because it will ignore them and the second condition is not to
have a 100% because it will be treated as 1.
My solution is very primitive, and i know that the other persons who
are more experienced will come with many better solutions, it is just
that i wanted to help.

Regards

 

[Mike H]

Hi,

With a helper column. Put this in B4 and drag right

=(LEFT( CELL("format",B3),1)="P")

The sum your range with

=SUMPRODUCT((B4:AM4*1=0)*(B3:AM3))

Mike