Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Sum function challenge !!!

8 views
Skip to first unread message

Alan

unread,
Jun 22, 2000, 3:00:00 AM6/22/00
to
Hello Group!
I have a question relate to range matching and sum up, please help!
Question:
A1:A2 = A, B
B1:B5 = B, C, B, D, A
C1:C5 = 1, 3, 4, 2, 30

I want to write a formula for D1 to sum C1:C5 base on the matching value
in B1:B5 with A1:A2. In this example, we need to find all A, B values in
B1:B5 and then sum C1:C5 coressponding to these A, B found in B1:B5. The
result for this example is = 1+4+30 = 35.

Thank you for all helps !.

--
Posted via CNET Help.com
http://www.help.com/

Robin

unread,
Jun 22, 2000, 3:00:00 AM6/22/00
to
=sumif(b1:b5,a1,c1:5)+sumif(b1:b5,a2,c1:c5)

Robin
Hope this helps you out
"Alan" <learn...@yahoo.com> wrote in message
news:sl51ata...@corp.supernews.com...

Tom Ogilvy

unread,
Jun 22, 2000, 3:00:00 AM6/22/00
to
Here are two answers you received to your previous posting of this question.

Regards,
Tom Ogilvy


From: "George Simms" <Geor...@email.msn.com>
References: <sl4mpf9...@corp.supernews.com>
Subject: Re: Sum function challenge ???
Date: Thu, 22 Jun 2000 19:59:26 +0100
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Message-ID: <eGMKLvH3$GA.176@cppssbbsa05>
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: host62-7-139-248.host.btclick.com 62.7.139.248
Path: cppssbbsa01.microsoft.com!cppssbbsa05
Xref: cppssbbsa01.microsoft.com microsoft.public.excel.programming:134484

Hi Alan

How about:

=SUMPRODUCT(SUMIF(B1:B5,A1:A2,C1:C5))

or

=SUMPRODUCT(SUMIF(B1:B5,{"A","B"},C1:C5))

or


=SUMPRODUCT((B1:B5=A1)+(B1:B5=A2),C1:C5)

All the Best

George
Microsoft MVP - Excel

Newcastle upon Tyne
England.


================================
Message-ID: <39525F48...@nospamer.com>
Date: Thu, 22 Jun 2000 13:47:37 -0500
From: nosp...@nospamer.com
X-Mailer: Mozilla 4.5 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Sum function challenge ???
References: <sl4mpf9...@corp.supernews.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: outbound5.enron.com 192.152.140.9
Path: cppssbbsa01.microsoft.com!cppssbbsa05
Lines: 18
Xref: cppssbbsa01.microsoft.com microsoft.public.excel.programming:134481

The following accomplishes your task

Cell D1 : =SUMIF($B$1:$B$5,A1,C1:C5)+SUMIF($B$1:$B$5,A2,C1:C5)

Hope this helps.

Alan

unread,
Jun 23, 2000, 3:00:00 AM6/23/00
to
Thank you George, Tom and all people responsed on this question!, the
SUMPRODUCT formula work for my case. I post this message twice because I
though the first one didn't go through. Sorry for the incovenience and
thanks again.
0 new messages