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)

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

Thanks, it works.

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/

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

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

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

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)

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

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/

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

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

Am I on the right track?


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