sumif question 2
 

the function as written can not be entered as an array formula. An invalid
formula is an invalid formula.
--
Regards,
Tom Ogilvy

" wrote in message
oups.com...

Dave H wrote:
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:S40)*(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