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

Advertisements

F

Frank Kabel

Hi
why don't you reply to your original threads?
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
 
Ad

Advertisements

T

Tom Ogilvy

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif question 5
SUMIF 1
Group and SUM 4
How doable Using Excel? 2
Union, intersection, join 1
Calculating formula ..circular references 1
2 sheets and a select 2
Help 3

Top