Kind-of extended sumif
 

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)

Kind-of extended sumif
 

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

BEDE wrote:
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)?

Kind-of extended sumif
 

Thanks, it works.

Kind-of extended sumif
 

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.


---
Message posted from http://www.ExcelForum.com/

Kind-of extended sumif
 

OR for the or
=SUMPRODUCT(--(A1:A999=13)+(A1:A999=17)0),A1:A999)

this might also work
=SUMPRODUCT((A1:A999={13,17}0),A1:A999)

--
Don Guillett
SalesAid Software

"Frank Kabel" wrote in message
...
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

BEDE wrote:
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)?

Kind-of extended sumif
 

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_colum n)

Jerry

BEDE wrote:

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)?

Kind-of extended sumif
 

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

--
Message posted from http://www.ExcelForum.com

Kind-of extended sumif
 

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

Andrew < wrote:

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 a
multicriteria alternative to Filters.

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

Kind-of extended sumif
 

Yes, I see it does. Thanks.

I have these formulas on a workbook as an array.

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

{=SUM((E2:E1050)*(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..

--
Message posted from http://www.ExcelForum.com

Kind-of extended sumif
 

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),E 2: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 :)


---
Message posted from http://www.ExcelForum.com/

Kind-of extended sumif
 

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),E 2:E10)
sums values from E2:E10 that are equal to 78 or 45 or 700.

Jerry

Andrew < wrote:

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),E 2: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 :)

Kind-of extended sumif
 

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

--
Message posted from http://www.ExcelForum.com

Kind-of extended sumif
 

Am I on the right track?


---
Message posted from http://www.ExcelForum.com/