SUM(SUMIF(*{ formula

A

acw

MrExcel MVP

Joined

Feb 13, 2004

Messages

4,814

• Aug 13, 2006

• #2

Hi

Assuming that you have your apples and oranges in the range A7:A8, then try this array entered (ctrl, shift, enter)

Code:

=SUM(SUMIF(A1:A5,A7:A8,B1:B5 ))

Tony

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Aug 13, 2006

• #3

Try:

=SUM(IF(ISNUMBER(MATCH(A1:A5,C1:C2,0)),B1:B5))

Which must be confirmed with Control + Shift + Enter

Where C1 contains "Apples"
and C2 contains "Oranges"

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Aug 13, 2006

• #4

hi - welcome to the board!

consider the following:

Book1
	A	B	C	D	E	F
1	apples	1		7		apples
2	oranges	3		7		oranges
3	oranges	3
4	melons	4
5	pears	5
6
7
Sheet1

...the { } indicate an array. in:

=SUM(SUMIF(A1:A5,{"apples","oranges"},B1:B5 ))

...it's an array constant - the text string "apples","oranges". in:

=SUM(SUMIF(A1:A5,F1:F2,B1:B5 ))

...it's an array formula. you don't enter them directly in the latter case - formula gets entered with control + shift + enter + excel adds the { }.

review excel help file for 'About array formulas and array constants'

 

Upvote 0

V

venkat1926

Well-known Member

Joined

Aug 21, 2005

Messages

4,824

• Aug 13, 2006

• #5

I think there is no need to refer only to cells

try this formula

=SUMPRODUCT((A1:A5="apples")*(B1:B5))+SUMPRODUCT((A1:A5="oranges")*(B1:B5))

 

Upvote 0

J

JohnnyTightlips

Board Regular

Joined

Aug 13, 2006

Messages

94

• Aug 13, 2006

• #6

Thanks to all for the quick replies.

The various solutions appear to require a contiguous range. Can I refer to non-contiguous cells? e.g. A2 & D4

Or am I just being difficult?! :wink:

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Aug 13, 2006

• #7

"Or am I just being difficult?! "

Not really

http://www.mrexcel.com/board2/viewtopic.php?t=29977

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Aug 13, 2006

• #8

Thanks to all for the quick replies.

The various solutions appear to require a contiguous range. Can I refer to non-contiguous cells? e.g. A2 & D4

Or am I just being difficult?! :wink:

Click to expand...

Hi Johnny:

Welcome to MrExcel Board!

Following is my convoluted formula that should work for you ...

=SUMPRODUCT(((ISNUMBER(MATCH(A1:A5,A2,0)))+(ISNUMBER(MATCH(A1:A5,D4,0)))),B1:B5)

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Aug 13, 2006

• #9

Hi Johnny:

Using A2, and D4 as ranges ... here is another convoluted formula of mine ...

=SUMPRODUCT((COUNTIF(INDIRECT({"a2","d4"}),A1:A5))*(B1:B5)+(ROW(1:5)=0))

Let me know how it goes!

 

Upvote 0

J

JohnnyTightlips

Board Regular

Joined

Aug 13, 2006

Messages

94

• Aug 14, 2006

• #10

Thanks to all for their help

I realise my "problem" as such could be easily solved using multiple SUMIFS or SUMPRODUCTS (my favourite formula!)

I was just attracted to the elegant simplicity of the array constant (thanks Paddy) SUMIF formula: if it could refer to relative (non-contig.) cell references it would be my new fave formula

Oh well, at least I have a new favourite excel forum

 

Upvote 0