multiple criteria sumif

nkhan

New Member
Joined
May 12, 2011
Messages
6
Hi, i need help with producing a sumif result from 2 if statements

have heard of DSUM but cannnot get it to work

I need to create a summary table with people and months and only show relevant infor per person per each month.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
If you need to create a summary table, I might recommend using a Pivot Table. Place your people and your months columns in the row field, and then what information you want to sum in the data field.
 
Upvote 0
My Plan A is a pivot table but also need a Plan B as need conditional formatting and as the data table increases, only then will all the fields appear but i need to set up straight away so data can be added over the year without any intervention from me.

i need something like sumif(a2:a25,"April",AND b2:b25,"John",sumC2:c25)
 
Last edited:
Upvote 0
Two different formulas you can use:

Excel 2003 or older:
=SUMPRODUCT(--(A2:A25="April"),--(B2:B25="John"),C2:C25)

Excel 2007 or newer:
=SUMIFS(C2:C25,A2:A25,"April",B2:B25,"John")

If you have Excel 2007, you can also use entire column references in the SUMIFS (and SUMIFS is significantly faster than SUMPRODUCT):
=SUMIFS(C:C,A:A,"April",B:B,"John")
 
Upvote 0
To count, we need to remove the sum references:

Excel 2003 or older:
=SUMPRODUCT(--(A2:A25="April"),--(B2:B25="John"))

Excel 2007 or newer:
=COUNTIFS(A2:A25,"April",B2:B25,"John")

And again, you can use entire column references for Excel 2007.
 
Upvote 0
Instead of SUMPRODUCT I have used COUNTPRODUCT but no luck. Results in #NAME?

What am I doing wrong? Do I need to amend the formula in any way?
 
Upvote 0
You need to use SUMPRODUCT. There is no such formula as COUNTPRODUCT.
 
Upvote 0
I want to do a count. Separate to the sum. If possible that is. I need ro count each person per month
 
Upvote 0
I understand that. SUMPRODUCT will do what you need.

Let’s use the following as an example:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Data:<o:p></o:p>
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Date</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Amount</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Type</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Branch</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">4000</td><td style=";">Deposit</td><td style=";">East</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">6000</td><td style=";">Deposit</td><td style=";">East</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">2000</td><td style=";">Withdrawl</td><td style=";">East</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">3000</td><td style=";">Deposit</td><td style=";">West</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">12/2/2010</td><td style="text-align: right;;">5000</td><td style=";">Withdrawl</td><td style=";">East</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">12/2/2010</td><td style="text-align: right;;">5000</td><td style=";">Withdrawl</td><td style=";">West</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">12/3/2010</td><td style="text-align: right;;">10000</td><td style=";">Deposit</td><td style=";">West</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">12/3/2010</td><td style="text-align: right;;">4000</td><td style=";">Withdrawl</td><td style=";">East</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

Problem: We want to count the number of Deposits in the East branch.

The SUMPRODUCT function takes two or more arrays, multiplies individual entries together, and sums the results (it sums the products of the arrays, hence the name). But before we can use this, we must first identify our conditions. In our problem, we want to count the number of Deposits in the East branch. If we look at this in a logic sense, we are asking:
<o:p></o:p>
C2:C9=”Deposit”
D2:D9=”East”
<o:p></o:p>
If we were to look at the data based on these conditions, it would have TRUE/FALSE for where the criteria is/isn’t met (this is purely for illustrative purposes to show what goes on behind-the-scenes):
<o:p></o:p>

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Date</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Amount</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Type</td><td style="font-weight: bold;text-align: center;text-decoration: underline;;">Branch</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">6000</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">2000</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12/1/2010</td><td style="text-align: right;;">3000</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">12/2/2010</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">12/2/2010</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">12/3/2010</td><td style="text-align: right;;">10000</td><td style="text-align: right;;">TRUE</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">12/3/2010</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">FALSE</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

<o:p></o:p>
Looking at this data, wherever there is a TRUE and TRUE statement, that is where both conditions are met, and should be counted. From here, there are two ways SUMPRODUCT can handle the data.


<o:p></o:p>
  • =SUMPRODUCT((C2:C9=”Deposit”)*(D2:D9=”East”))
  • =SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”))
<o:p></o:p>
Both formulas will return the desired result (2), but they evaluate in very different ways.


<o:p></o:p>
  • The first formula, =SUMPRODUCT((C2:C9=”Deposit”)*(D2:D9=”East”)), creates one array within the formula, based on the conditions:

    The first condition: (C2:C9=”Deposit”) returns the array {TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}

    The second condition: (D2:D9=”East”) returns the array
    {TRUE,TRUE,TRUE,FALSE,TRUE,FALSE,FALSE,TRUE}

    When multiplied together (as the formula shows), a new array is returned:
    {TRUE*TRUE, TRUE*TRUE, FALSE*TRUE, TRUE*FALSE, FALSE*TRUE, FALSE*FALSE, TRUE*FALSE, FALSE*TRUE}

    When we multiply TRUE and FALSE statements together, Excel uses their values of 1 and 0 to evaluate and return a number. So
    TRUE*FALSE = 1*0 = 0
    FALSE*FALSE = 0*0 = 0
    FALSE*TRUE = 0*1 = 0
    TRUE*TRUE = 1*1 = 1

    After performing the mathematical operation, we have an array of 1 and 0s:
    {1,1,0,0,0,0,0,0}

    Now, SUMPRODUCT can add up the values! 1+1+0+0+0+0+0+0 = 2

  • The second formula, =SUMPRODUCT(--(C2:C9=”Deposit”),--(D2:D9=”East”)), evaluates in a similar fashion, except it uses multiple arrays. It holds some advantages over the example not using the --, if you want to read more in depth on this, please read xlDynamic’s full write-up on SUMPRODUCT here.

    First, the -- (double unary) must be explained. The -- coerces the conditional statement within the parentheses into a 1 or a 0. By doing this, it creates an array of 1s and 0s. It does this by taking each TRUE/FALSE statement and multiplying it by -1 twice. Since TRUE/FALSE holds values of 1/0 (as shown in explanation 1), we multiply them by -1 twice to convert them to a positive, numerical, value:

    --(TRUE) = (-1)(-1)(TRUE) = (-1)(-1) = 1
    --(FALSE) = (-1)(-1)(FALSE) = (-1)(0) = 0

    So now, SUMPRODUCT has two arrays to look at:

    {1,1,0,1,0,0,1,0} and {1,1,0,0,0,0,0,0}

    Following the nature of the SUMPRODUCT formula, it now multiplies these two arrays together and adds those products:

    1*1 + 1*1 + 0*0 + 1*0 + 0*0 + 0*0 + 1*0 + 0*0 = 1+1+0+0+0+0+0+0 = 2
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top