If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT)
Hello,
I need help with multiple criterias in Excel. From earlier messages in this group I have understood that I should use SUMPRODUCT but I can't get it to work properly. Excel just gives me the output #VALUE!. (I use Excel 2000.) My problem is the following: Month Person USD Apr 1 20 Apr 2 30 May 1 20 May 2 20 =SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5) I would like to sum the USD when the month is April and the person is number 1. Do I need to use Control+Shift+Enter or can I just use Enter to confirm the formula? (Unfortunately nothing of this works in the example above.) Bye, Cecilia |
#2
|
|||
|
|||
Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT)
I think the issue is that you're including the headers in
your ranges (row 1), and you end up trying to multiply a FALSE with a text string (USD) which results in an error. Change all your ranges to start with row 2 (like A2:A5). HTH Jason Atlanta, GA -----Original Message----- Hello, I need help with multiple criterias in Excel. From earlier messages in this group I have understood that I should use SUMPRODUCT but I can't get it to work properly. Excel just gives me the output #VALUE!. (I use Excel 2000.) My problem is the following: Month Person USD Apr 1 20 Apr 2 30 May 1 20 May 2 20 =SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5) I would like to sum the USD when the month is April and the person is number 1. Do I need to use Control+Shift+Enter or can I just use Enter to confirm the formula? (Unfortunately nothing of this works in the example above.) Bye, Cecilia . |
#3
|
|||
|
|||
Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT)
Change the range to exclude the headers, ie
=SUMPRODUCT((A2:A5="Apr")*(B2:B5=1)*C2:C5) Other than that, what you have should work, AS LONG as the Apr May etc that we are seeing in the range A2:A5 are really that, and are not dates fields formatted as mmm. Side-note, you have omitted the last set of parentheses from the C2:C5 which is absolutely correct, BUT, leaving them in can make it easier for somebody else to follow the thread of the formula. Won't affect anything, but can make it easier to read ;-) =SUMPRODUCT((A2:A5="Apr")*(B2:B5=1)*(C2:C5)) Also, if you intended to copy that formula down or across at all, make sure you lock the references, otherwise they will all change, eg:- =SUMPRODUCT(($A$2:$A$5="Apr")*($B$2:$B$5=1)*($C$2: $C$5)) Just following on from that logic, you may want to list the Months in a range, eg H1:H12 and then in I1 put the formula, but refer to the value in cell H1 for your month argument, eg:- =SUMPRODUCT(($A$2:$A$5=$H1)*($B$2:$B$5=1)*($C$2:$C $5)) You could now copy this down to I12 and it would pick up the month from the cell to the left of the formula each time. Note that i only locked the H part of the reference, as otherwise the row reference wouldn't increment each time. Copying down you will see this:- =SUMPRODUCT(($A$2:$A$5=$H1)*($B$2:$B$5=1)*($C$2:$C $5)) =SUMPRODUCT(($A$2:$A$5=$H2)*($B$2:$B$5=1)*($C$2:$C $5)) =SUMPRODUCT(($A$2:$A$5=$H3)*($B$2:$B$5=1)*($C$2:$C $5)) =SUMPRODUCT(($A$2:$A$5=$H4)*($B$2:$B$5=1)*($C$2:$C $5)) =SUMPRODUCT(($A$2:$A$5=$H5)*($B$2:$B$5=1)*($C$2:$C $5)) etc............ -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Cecilia" wrote in message om... Hello, I need help with multiple criterias in Excel. From earlier messages in this group I have understood that I should use SUMPRODUCT but I can't get it to work properly. Excel just gives me the output #VALUE!. (I use Excel 2000.) My problem is the following: Month Person USD Apr 1 20 Apr 2 30 May 1 20 May 2 20 =SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5) I would like to sum the USD when the month is April and the person is number 1. Do I need to use Control+Shift+Enter or can I just use Enter to confirm the formula? (Unfortunately nothing of this works in the example above.) Bye, Cecilia --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.562 / Virus Database: 354 - Release Date: 16/01/2004 |
#4
|
|||
|
|||
Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT)
"Cecilia" wrote...
... My problem is the following: Month Person USD Apr 1 20 Apr 2 30 May 1 20 May 2 20 =SUMPRODUCT((A1:A5="Apr")*(B1:B5=1)*C1:C5) ... Problem is C1 is text. That's a problem for the * operator, but not for SUMPRODUCT. As an alternative to the other solutions provided already, try =SUMPRODUCT((A1:A5="Apr")*(B1:B5=1),C1:C5) [This solution, obvious in hindsight (ain't that always the case), was given by Dana DeLouis a few weeks ago.] -- To top-post is human, to bottom-post and snip is sublime. |
Thread Tools | |
Display Modes | |
|
|