sumif question 2

  • Thread starter Thread starter Dave H
  • Start date Start date
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"?
 
Hi
why don't you reply to your original threads?
It won't help if you ask this question again and again!
 
Same as Frank told you on your first posting of this question today:

Hi
SUMIF(A1:A4,"S1",B1:B4)
 
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"?
Click to expand...

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
 
the function as written can not be entered as an array formula. An invalid
formula is an invalid formula.
 
Back
Top