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
|
|||
|
|||
Excel 2007 -- SUMIFS v SUMPRODUCT
I have had some very useful help from Jacob on an older version of Excel
where he sorted my problem out with SUMPRODUCT (see Problem with Syntax? posted below): =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D220000+E2:E20000)) The above formula works great, but I have two questions for Excel 2007 users: 1. If I convert B2:B20000 to B:B, why do I get a #VALUE error 2. Will SUMIFS do the above and if so, how? Thanks |
#2
|
|||
|
|||
Excel 2007 -- SUMIFS v SUMPRODUCT
Hi,
you have to change C , D and E as well, all the ranges in the formula has to be the same =SUMPRODUCT(--(TEXT(B:B,"mmyyyy")="012008"),--(C:C="Motor"),(D+E:E)) "MurrayBarn" wrote: I have had some very useful help from Jacob on an older version of Excel where he sorted my problem out with SUMPRODUCT (see Problem with Syntax? posted below): =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D220000+E2:E20000)) The above formula works great, but I have two questions for Excel 2007 users: 1. If I convert B2:B20000 to B:B, why do I get a #VALUE error 2. Will SUMIFS do the above and if so, how? Thanks |
#3
|
|||
|
|||
Excel 2007 -- SUMIFS v SUMPRODUCT
I tried that already but I get #VALUE error. I dont mind using 20000 but it
is a bit inelegant Also, will SUMIFS work? "Eduardo" wrote: Hi, you have to change C , D and E as well, all the ranges in the formula has to be the same =SUMPRODUCT(--(TEXT(B:B,"mmyyyy")="012008"),--(C:C="Motor"),(D+E:E)) "MurrayBarn" wrote: I have had some very useful help from Jacob on an older version of Excel where he sorted my problem out with SUMPRODUCT (see Problem with Syntax? posted below): =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D220000+E2:E20000)) The above formula works great, but I have two questions for Excel 2007 users: 1. If I convert B2:B20000 to B:B, why do I get a #VALUE error 2. Will SUMIFS do the above and if so, how? Thanks |
#4
|
|||
|
|||
Excel 2007 -- SUMIFS v SUMPRODUCT
Hi,
That make not sense, formula is OK, please check if in D or E you dont have any N/A or other error value, as well check that column B is format as text "MurrayBarn" wrote: I tried that already but I get #VALUE error. I dont mind using 20000 but it is a bit inelegant Also, will SUMIFS work? "Eduardo" wrote: Hi, you have to change C , D and E as well, all the ranges in the formula has to be the same =SUMPRODUCT(--(TEXT(B:B,"mmyyyy")="012008"),--(C:C="Motor"),(D+E:E)) "MurrayBarn" wrote: I have had some very useful help from Jacob on an older version of Excel where he sorted my problem out with SUMPRODUCT (see Problem with Syntax? posted below): =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D220000+E2:E20000)) The above formula works great, but I have two questions for Excel 2007 users: 1. If I convert B2:B20000 to B:B, why do I get a #VALUE error 2. Will SUMIFS do the above and if so, how? Thanks |
#5
|
|||
|
|||
Excel 2007 -- SUMIFS v SUMPRODUCT
Hi
The below links should answer your question 1 (On the limitation) http://support.microsoft.com/default.aspx/kb/166342 On using SUMIFS (this will be helpful) http://techtites.com/2008/05/22/exce...le-conditions/ If this post helps click Yes --------------- Jacob Skaria "MurrayBarn" wrote: I have had some very useful help from Jacob on an older version of Excel where he sorted my problem out with SUMPRODUCT (see Problem with Syntax? posted below): =SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D220000+E2:E20000)) The above formula works great, but I have two questions for Excel 2007 users: 1. If I convert B2:B20000 to B:B, why do I get a #VALUE error 2. Will SUMIFS do the above and if so, how? Thanks |
Thread Tools | |
Display Modes | |
|
|