×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Engineering Computer Programs > Engineering Programs > -Engineering spreadsheets > FAQs

-Engineering spreadsheets FAQ

Engineering spreadsheets

How does SUMPRODUCT() work for Multiple Criteria Aggregations? by SkipVought
faq770-1959
Posted: 29 Jan 17 (Edited 30 Jan 17)

I most often need to SUM or COUNT rows in a table that meet a set of criteria. The formulae that can be used to accomplish this task include:

SUMIFS(), COUNTIFS(), SUMPRODUCT()

I like using SUMPRODUCT() because it is more intuitive than the other two.

Here's an example of a simple Structured Table:

Table: tSample		
Date    	Name	Amt
2/1/2017	Al	22
2/13/2017	Al	33
2/25/2017	Al	23
2/5/2017	Bob	32
2/21/2017	Bob	21
3/9/2017	Bob	12
2/6/2017	Fred	42
2/22/2017	Fred	24
3/3/2017	Al	24
3/17/2017	Bob	14

Here's a summary table that uses SUMPRODUCT():

Name	29-Jan	12-Feb	26-Feb	12-Mar	26-Mar
Al	22	56	24	0	
Bob	32	21	12	14	
Fred	42	24	0	0

This is the formula here:
=SUMPRODUCT((tSample[Date]>=C$1)*(tSample[Date]<D$1)*(tSample[Name]=$A2)*(tSample[Amt]))

Here's how this works:
Table: tSample		   12-Feb           	26-Feb                          			SUM=56
Date       Name	Amt	   (tSample[Date]>=F$1)	* (tSample[Date]<G$1) * (tSample[Name]=$E2) * (tSample[Amt])	
2/1/2017   Al	22	Al FALSE           	* TRUE                * TRUE                * 22      =	    0
2/13/2017  Al	33	Al TRUE            	* TRUE                * TRUE                * 33      =	    33
2/25/2017  Al	23	Al TRUE            	* TRUE                * TRUE                * 23      =	    23
2/5/2017   Bob	32	   FALSE           	* TRUE                * FALSE               * 32      =	    0
2/21/2017  Bob	21	   TRUE            	* TRUE                * FALSE               * 21      =	    0
3/9/2017   Bob	12	   TRUE            	* FALSE               * FALSE               * 12      =     0
2/6/2017   Fred	42	   FALSE           	* TRUE                * FALSE               * 42      =	    0
2/22/2017  Fred	24	   TRUE             	* TRUE                * FALSE               * 24      =	    0
3/3/2017   Al	24	Al TRUE            	* FALSE               * TRUE                * 24      =	    0
3/17/2017  Bob	14	   TRUE            	* FALSE               * FALSE               * 14      =	    0

So any row where there are all TRUE , the PRODUCT calculates. Otherwise ZERO. Then you get the SUM of the PRODUCTS. If you were to eliminate the (tSample[Amt]) in the SUMPRODUCT, you'll get a COUNT of the rows that meet all the criteria.

Back to -Engineering spreadsheets FAQ Index
Back to -Engineering spreadsheets Forum


My Archive


News
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close