Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / March 2008

Tip: Looking for answers? Try searching our database.

Sum Multiple Matches

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mart - 25 Mar 2008 13:00 GMT
Is there a way to sum totals based on multiple matches?  Say in the following
example, from a separate sheet, i want to sum up the totals for stationary on
the first sheet.

e.g.

Expense Type    Total
stationary           £15
car                     £140
instruments         £150
stationary           £25

...so I want to return the 15+25 based on a lookup (assume lookup value to
be a given/stated i.e. "stationary").  Range will be constant size and named
e.g. "JanExpenses"

N.b. there will be 12 sheets, each with similar lists (1 for each month)
which i would then need to sum, so the above might just be for January.  I
would then want to add this to Feb-Dec returned totals

thanks
Mike H - 25 Mar 2008 13:10 GMT
Hi,

Something along these lines

=SUMPRODUCT((Sheet2!A2:A5="Stationary")*(Sheet2!B2:B5))

Mike

> Is there a way to sum totals based on multiple matches?  Say in the following
> example, from a separate sheet, i want to sum up the totals for stationary on
[quoted text clipped - 17 lines]
>
> thanks
Mart - 25 Mar 2008 16:01 GMT
thanks Mike , that's perfect.  

I think Bernard's suggestion would have meant additional summing on the
individual sheets so the Sumproduct option is ideal.

..just glad i don't need to use Index match type solution !

> Hi,
>
[quoted text clipped - 25 lines]
> >
> > thanks
Mike H - 25 Mar 2008 16:36 GMT
Mart,

Your welcome and thanks for the feedback

Mike

> thanks Mike , that's perfect.  
>
[quoted text clipped - 32 lines]
> > >
> > > thanks
Bernard Liengme - 25 Mar 2008 13:14 GMT
In each sheet in same cell (say Z1)
=SUMIF(A1:A100,"stationary",B1:B100)
On summary sheet =SUM('Sheet1:Sheet10'!Z1)
best wishes
Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Is there a way to sum totals based on multiple matches?  Say in the
> following
[quoted text clipped - 20 lines]
>
> thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.