Kind-of extended sumif

G

Guest

I'd like to have something like a sumif only that I wish to have some complex conditions, like
"=13 or =17 or =22" instead of the simple "=13", which requires me to have 3 sumifs, one for each of the 3 conditions that I wish to have or-ed
Is there anything ready-made

Or is there some function that performs like "eval" in Foxpro (I know that since Fox 2.0)
 
F

Frank Kabel

Hi
you can use SUMPRODUCT for this issue.
e.g. AND condition:
=SUMPRODUCT((A1:A999=13)*(A1:A999=17),A1:A999)
e.g. OR condition
=SUMPRODUCT(--(A1:A999=13)+(A1:A999=17)>0),A1:A999)
HTH
Frank
 
A

Andrew

Here are a couple of alternatives for the AND condition.

=SUM(IF(A1:A999=13,IF(A1:A999=17,A1:A999)))

=SUM((A1:A999=13)*(A1:A999=17)*A1:A999)

Don't forget to push Shift, Control & Enter simultaneously instead of
just Enter.
 
J

Jerry W. Lewis

In addition to the other suggestions, the following will work if you are
talking about data in a column

=SUMPRODUCT((<check_column>={13,17,22})*<sum_column>)

Jerry
 
A

Andrew

Hey Guys,

Is there a special name for this kind of formula?

=SUM(IF(A1:A999=13,IF(A1:A999=17,A1:A999)))

I was pretending to work yesterday and came up with it as
multicriteria alternative to Filters.

If nobody can prove prior credit, I hereby name it a Nested SumI
Array! (Hey, I'm just a newbie, gimme a break
 
J

Jerry W. Lewis

Your formula can be simplified to 0.

Your inner IF returns an array with FALSE in every row except where
there is a 17 in A1:A999 (where it returns 17). Since there are no 13's
in the array produced by the inner IF, the outer IF returns an array of
999 FALSE values; therefore SUM returns zero.

Jerry
 
A

Andrew

Yes, I see it does. Thanks.

I have these formulas on a workbook as an array.

{=SUM(IF(E2:E10>50,IF(E2:E10<300,E2:E10)))}

{=SUM((E2:E10>50)*(E2:E10<300)*E2:E10)}

E2:E10 = 23, 300, 78, 45, 56, 700, 2,000, 55, 89.

The above formulas give an answer of 278. Both seem to work with > or
but not =.

I tried this type of array formula for multiple columns before, thi
was the first time I tried it with a single column.

Back to the drawing board. I just started experimenting with arrays
few days ago so I'll try testing them more before embarrassing mysel
:)

Those SUMPRODUCT formulas are interesting. Must have a good look to se
how they work..
 
A

Andrew

I tried some of the above SUMPRODUCT array formulas in a single column
but did not have much luck. Maybe I'm doing it wrong.

These are two that seem to work as multicriteria SUMIF and COUNTIF
formulas within a single column of numbers.

=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700),E2:E10)
Answer = 823

=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700))
Answer = 3

Note that all three numbers, 78, 45 and 700 appear within the E2:E10
range. Changing any of the three numbers within the range will effect
the answer (obviously?)

Any feedback or suggestions? Never too old to learn something new :)
 
J

Jerry W. Lewis

To combine logicals, * corresponds to AND and + corresponds to OR. Thus
(E2:E10=78)
returns an array with TRUE (1) in positions correspond to values of 78
in E2:E10 and FALSE (0) elsewhere
(E2:E10=78)+(E2:E10=45)+(E2:E10=700)
returns an array with 1 in positions that correspond to values of 78 or
45 or 700 in E2:E10 and 0 elsewhere. Hence
=SUMPRODUCT((E2:E10=78)+(E2:E10=45)+(E2:E10=700),E2:E10)
sums values from E2:E10 that are equal to 78 or 45 or 700.

Jerry
 
A

Andrew

Thanks Jerry,

Now I think I am beginning to understand. You can use OR formulas in
single or multiple columns but AND formulas can only be used i
multiple columns, otherwise the answer will be zero, right?

In a single column, an AND formula will try to match all of th
criteria against the value in a cell, and because not all the criteri
will match the same value it will return FALSE? In the case of an O
formula, if one of the criteria is matched the result will be TRUE?

I use both AND and OR formulas but using them in arrays is a new thin
for me.

Changing the subject, I notice that these two formulas seem to give th
same result.

=SUMPRODUCT((B2:B10="abc")*(C2:C10="gizmo")*E2:E10)

=SUM((B2:B10="abc")*(C2:C10="gizmo")*E2:E10)

Andre
 

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

Top