sumif question 2

[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"?

 

[Frank Kabel]

Hi
why don't you reply to your original threads?
It won't help if you ask this question again and again!

 

[Tom Ogilvy]

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

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

 

[marston.gould]

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

 

[Tom Ogilvy]

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