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/