| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif
The formula returns a 0. I need it to return $38,241.51
=SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
A couple of things... I column Q actual dates that have been formatted mm/yy
or is it text. If it is text then your criteria needs to be in the same format and not "Apr-08". Secondly try using the unary operator -- to coerce the true/false values to 1/0 values. =SUMPRODUCT(--(Data!B1:B20000="ABC Company"), --(Data!Q1:Q20000="Apr-08"), (L1:L20000)) if Q is actual dates then =SUMPRODUCT(--(Data!B1:B20000="ABC Company"), --(Month(Data!Q1:Q20000)=4), --(Year(Data!Q1:Q20000)=2008), (L1:L20000)) -- HTH... Jim Thomlinson "MadWoman" wrote: The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
Hi
=SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
You can use =SUMPRODUCT((B1:B20000="ABC Company")*(Q1:Q20000=DATEVALUE("1
April 2008"))*(Q1:Q20000<=DATEVALUE("30 April 2008"))*(L1:L20000)) Andrea Jones www.stratatraining.co.uk "MadWoman" wrote: The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT
field. "Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
PS I'm using Excel 2002.
"Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
I tried this and still get a return value of 0. PS I'm using Excel 2002.
"Andrea Jones" wrote: You can use =SUMPRODUCT((B1:B20000="ABC Company")*(Q1:Q20000=DATEVALUE("1 April 2008"))*(Q1:Q20000<=DATEVALUE("30 April 2008"))*(L1:L20000)) Andrea Jones www.stratatraining.co.uk "MadWoman" wrote: The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
Thanks for your help.
I originally entered the data with a ' in front of Apr-08 and have since changed column Q to a text field. I still get 0. I tried changing column Q to a date field as you suggested and still get 0 for a result. I'm so confused. "Jim Thomlinson" wrote: A couple of things... I column Q actual dates that have been formatted mm/yy or is it text. If it is text then your criteria needs to be in the same format and not "Apr-08". Secondly try using the unary operator -- to coerce the true/false values to 1/0 values. =SUMPRODUCT(--(Data!B1:B20000="ABC Company"), --(Data!Q1:Q20000="Apr-08"), (L1:L20000)) if Q is actual dates then =SUMPRODUCT(--(Data!B1:B20000="ABC Company"), --(Month(Data!Q1:Q20000)=4), --(Year(Data!Q1:Q20000)=2008), (L1:L20000)) -- HTH... Jim Thomlinson "MadWoman" wrote: The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
Hi
I missed a closing parenthesis in my formula. It should have read =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy")="Apr-08")*(L1:L20000)) However, you say your dates are Text and not true Excel dates. You also said in your original post that column C was formatted MM/YY which would be 04/08 Perhaps you need =SUMPRODUCT((Data!B1:B20000="ABC Company")* (Data!Q1:Q20000="04/08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT field. "Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
Roger: Thanks for taking the time to help me with this. I could still use
your help if you are willing. I've been thinking about it and have other questions. I'm using SUMPRODUCT in other spreadsheets and it works well for, of all things, COUNTING. That said, I don't understand how SUMPRODUCT will sum up the Subtotaled $$ Figures in Column L. I'm trying to get a formula together that if the two conditions are met, that the COLUMN L value returned is the SUM of the two conditions, not a count. I've been dinking w/ the formula editor & have changed Columns B & Q to Text and made all my addition/multiplacation fields in the same format. PS. I'm using Excel 2002. Thanks again for your help and look forward to hearing from you. Lost in New Mexico, Madelyn. "Roger Govier" wrote: Hi I missed a closing parenthesis in my formula. It should have read =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy")="Apr-08")*(L1:L20000)) However, you say your dates are Text and not true Excel dates. You also said in your original post that column C was formatted MM/YY which would be 04/08 Perhaps you need =SUMPRODUCT((Data!B1:B20000="ABC Company")* (Data!Q1:Q20000="04/08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT field. "Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
Hi Madelyn
Send me a copy of the file To mail direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "MadWoman" wrote in message ... Roger: Thanks for taking the time to help me with this. I could still use your help if you are willing. I've been thinking about it and have other questions. I'm using SUMPRODUCT in other spreadsheets and it works well for, of all things, COUNTING. That said, I don't understand how SUMPRODUCT will sum up the Subtotaled $$ Figures in Column L. I'm trying to get a formula together that if the two conditions are met, that the COLUMN L value returned is the SUM of the two conditions, not a count. I've been dinking w/ the formula editor & have changed Columns B & Q to Text and made all my addition/multiplacation fields in the same format. PS. I'm using Excel 2002. Thanks again for your help and look forward to hearing from you. Lost in New Mexico, Madelyn. "Roger Govier" wrote: Hi I missed a closing parenthesis in my formula. It should have read =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy")="Apr-08")*(L1:L20000)) However, you say your dates are Text and not true Excel dates. You also said in your original post that column C was formatted MM/YY which would be 04/08 Perhaps you need =SUMPRODUCT((Data!B1:B20000="ABC Company")* (Data!Q1:Q20000="04/08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT field. "Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif
File received and returned.
there were a few entries in the date column that were Text entries, either because they had been entered as text, or because there was some extra text typed after the date itself. Once the column of data was "cleaned", Sumproduct worked fine. I added some named ranges of the form =Data!$D$4:INDEX(Data!$B:$B,COUNTA(Data!$B:$B)+2) and then the formula =SUMPRODUCT((Vendor=$B4)*(MONTH(Invdate)=MONTH(H$3 ))*(Value)) Where H3:S3 contained dates for each of the 12 months that Madelyn required the Summary for. I would have suggested using a Pivot Table instead, but lots of blanks within Invdate would have prevented grouping by Month. -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi Madelyn Send me a copy of the file To mail direct roger at technology4u dot co dot uk Change the at and dots to make a valid email address. -- Regards Roger Govier "MadWoman" wrote in message ... Roger: Thanks for taking the time to help me with this. I could still use your help if you are willing. I've been thinking about it and have other questions. I'm using SUMPRODUCT in other spreadsheets and it works well for, of all things, COUNTING. That said, I don't understand how SUMPRODUCT will sum up the Subtotaled $$ Figures in Column L. I'm trying to get a formula together that if the two conditions are met, that the COLUMN L value returned is the SUM of the two conditions, not a count. I've been dinking w/ the formula editor & have changed Columns B & Q to Text and made all my addition/multiplacation fields in the same format. PS. I'm using Excel 2002. Thanks again for your help and look forward to hearing from you. Lost in New Mexico, Madelyn. "Roger Govier" wrote: Hi I missed a closing parenthesis in my formula. It should have read =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy")="Apr-08")*(L1:L20000)) However, you say your dates are Text and not true Excel dates. You also said in your original post that column C was formatted MM/YY which would be 04/08 Perhaps you need =SUMPRODUCT((Data!B1:B20000="ABC Company")* (Data!Q1:Q20000="04/08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... OK, now I get a return error of #VALUE! Now what? PS. Column Q is a TEXT field. "Roger Govier" wrote: Hi =SUMPRODUCT((Data!B1:B20000="ABC Company")* (TEXT(Data!Q1:Q20000,"mmm-yy"="Apr-08")*(L1:L20000)) -- Regards Roger Govier "MadWoman" wrote in message ... The formula returns a 0. I need it to return $38,241.51 =SUMPRODUCT((Data!B1:B20000="ABC Company")*(Data!Q1:Q20000="Apr-08")*(L1:L20000))The formula below returns a 0. I need it to return $38,241.51. Column B = Company Name Column Q = MM/YY Column L = Subtotal dollar amount What am I missing and how can this be corrected to return the correct value. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| sumif help... | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
