# sumif question 2

D

#### Dave H

I would like to do a sumif and have logical statement to
filter sum values out.
Example:

A Column B Column
S4 1
S5 2
S2 3
S1 100

Sumif(a 1 to 4, <>"s4",<>"s5","<>"S2", b 1 to 4)
= 100.
How do I right the expression to only add the "S1"?

F

#### Frank Kabel

Hi
It won't help if you ask this question again and again!

T

#### Tom Ogilvy

Same as Frank told you on your first posting of this question today:

Hi
SUMIF(A1:A4,"S1",B1:B4)

M

#### marston.gould

Dave said:
I would like to do a sumif and have logical statement to
filter sum values out.
Example:

A Column B Column
S4 1
S5 2
S2 3
S1 100

Sumif(a 1 to 4, <>"s4",<>"s5","<>"S2", b 1 to 4)
= 100.
How do I right the expression to only add the "S1"?

Dave -

What you actually need to do is use worksheet array function
To get array functions, you need to enter your formula and hit
Ctrl+Shift+Enter

such as:

={Sum((S1:S4>0)*(S1:S4<100)*S1:S4)} - this will give you the sum of
all values greater than 0 and less than 100, you can modify as you like
to suit your needs. There's a good write up of this in John
Walkenbach's Excel Formulas book

T

#### Tom Ogilvy

the function as written can not be entered as an array formula. An invalid
formula is an invalid formula.