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 / May 2008

Tip: Looking for answers? Try searching our database.

Use SUMPRODUCT for a matrix

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Learn-more - 23 May 2008 09:08 GMT
The case is I want to key in two criteria and the function give me back the
total. For example, I want Sun in the rows and Two in the column, then will
give me the total of  2 + 8 = 10. The Sun and the Two is input some where and
will be change from time to time.
       A      B    C    D
1            One        Two       Three
2       Sun      1     2    3   
3       Mon       4             5             6
4       Sun      7             8             9

I tried using SUMPRODUCT but did not seem to work, may be due to my
knowledge is not good enough, anyone know how or have other functions that
make it work?

Thanks very much.
Mike H - 23 May 2008 09:16 GMT
Hi,

Try this

=SUMPRODUCT((A2:A4="Sun")*(B1:D1="Two")*(B2:D4))

To make it more flexible it would be better to use cell references for the
criteria

=SUMPRODUCT((A2:A4=E1)*(B1:D1=E2)*(B2:D4))

Mike

> The case is I want to key in two criteria and the function give me back the
> total. For example, I want Sun in the rows and Two in the column, then will
[quoted text clipped - 11 lines]
>
> Thanks very much.
Learn-more - 23 May 2008 09:41 GMT
Hi Mike,

That works, thank you.

> Hi,
>
[quoted text clipped - 24 lines]
> >
> > Thanks very much.
Mike H - 23 May 2008 10:26 GMT
Your welcome and thanks for the feedback

> Hi Mike,
>
[quoted text clipped - 28 lines]
> > >
> > > Thanks very much.
 
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.