Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
I have been working for over an hour to try and figure out why I am not
getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
You either have to modify these formulas:
G2, formula =left(b2,4) H2, formula =left(f2,2) Or, modify the SUMPRODUCT formula. The RIGHT and LEFT formulas return *TEXT* even the the result is a string of numbers. Your SUMP formula is testing those ranges for NUMBERS. These will coerce TEXT numbers into NUMERIC numbers: If the formula in G extracts the year: =--LEFT(B2,4) If the formula in H extracts the month: =--LEFT(F2,2) Or, you can quote the TEXT numbers in the SUMP formula: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="1"),--(Sheet2!G2:G50000="2008"),D2:D50000) -- Biff Microsoft Excel MVP "Tasha" wrote in message ... I have been working for over an hour to try and figure out why I am not getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
Those left and right formulae will be returning text values, so yo wil
have to put quotes around the numbers like this: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="01"),-- (Sheet2!*G2:G50000="2008"),D2:D50000) Hope this helps. Pete On Aug 27, 10:21*pm, Tasha wrote: I have been working for over an hour to try and figure out why I am not getting this formula right. *Can someone please see if they can figure out what I'm doing wrong? *Is it my formats maybe? *I keep getting 0 or #VALUE! My data: A * * * B * * *C * * * D * * E * * * *F * * * * * * * *G * *H * * id# * date *code *qty *amt * *month/day * yr * *month C is formatted as text in F2, formula =right(b2,5) * * copied down in G2, formula =left(b2,4) * * * copied down in H2, formula =left(f2,2) * * * *copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!*G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
=SUMPRODUCT((erlvltrnd!C2:C35000="199000"),--(erlvltrnd!H2:H35000="1"),--(erlvltrnd!G2:G35000="2008"),D2:D35000)
I used the formula above, and still nothing, I am getting a 0 Ok, is there another way maybe I need to import the data? I'm importing a text file. I tried importing the date as general, and I still couldn't get it to work. Or is there another way I could pull the month/year from the date without breaking it out into other columns? "T. Valko" wrote: You either have to modify these formulas: G2, formula =left(b2,4) H2, formula =left(f2,2) Or, modify the SUMPRODUCT formula. The RIGHT and LEFT formulas return *TEXT* even the the result is a string of numbers. Your SUMP formula is testing those ranges for NUMBERS. These will coerce TEXT numbers into NUMERIC numbers: If the formula in G extracts the year: =--LEFT(B2,4) If the formula in H extracts the month: =--LEFT(F2,2) Or, you can quote the TEXT numbers in the SUMP formula: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="1"),--(Sheet2!G2:G50000="2008"),D2:D50000) -- Biff Microsoft Excel MVP "Tasha" wrote in message ... I have been working for over an hour to try and figure out why I am not getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
I figured it out....thank you though.
I set the query that I used to build the file to break out the month/year from the date. Imported the file to Excel, then set up #'s above the month fields for year and month by typing in 1-12 for month and 8, then 9 for each year. I referred back to these fields in my sumproduct formulas, and I took the quotes out from around 199000, and it gave me my total. :) "T. Valko" wrote: You either have to modify these formulas: G2, formula =left(b2,4) H2, formula =left(f2,2) Or, modify the SUMPRODUCT formula. The RIGHT and LEFT formulas return *TEXT* even the the result is a string of numbers. Your SUMP formula is testing those ranges for NUMBERS. These will coerce TEXT numbers into NUMERIC numbers: If the formula in G extracts the year: =--LEFT(B2,4) If the formula in H extracts the month: =--LEFT(F2,2) Or, you can quote the TEXT numbers in the SUMP formula: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="1"),--(Sheet2!G2:G50000="2008"),D2:D50000) -- Biff Microsoft Excel MVP "Tasha" wrote in message ... I have been working for over an hour to try and figure out why I am not getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
I figured it out....thank you though.
I set the query that I used to build the file to break out the month/year from the date. Imported the file to Excel, then set up #'s above the month fields for year and month by typing in 1-12 for month and 8, then 9 for each year. I referred back to these fields in my sumproduct formulas, and I took the quotes out from around 199000, and it gave me my total. :) "Pete_UK" wrote: Those left and right formulae will be returning text values, so yo wil have to put quotes around the numbers like this: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="01"),-- (Sheet2!Â*G2:G50000="2008"),D2:D50000) Hope this helps. Pete On Aug 27, 10:21 pm, Tasha wrote: I have been working for over an hour to try and figure out why I am not getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!Â*G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with SUMPRODUCT
OK, good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tasha" wrote in message ... I figured it out....thank you though. I set the query that I used to build the file to break out the month/year from the date. Imported the file to Excel, then set up #'s above the month fields for year and month by typing in 1-12 for month and 8, then 9 for each year. I referred back to these fields in my sumproduct formulas, and I took the quotes out from around 199000, and it gave me my total. :) "T. Valko" wrote: You either have to modify these formulas: G2, formula =left(b2,4) H2, formula =left(f2,2) Or, modify the SUMPRODUCT formula. The RIGHT and LEFT formulas return *TEXT* even the the result is a string of numbers. Your SUMP formula is testing those ranges for NUMBERS. These will coerce TEXT numbers into NUMERIC numbers: If the formula in G extracts the year: =--LEFT(B2,4) If the formula in H extracts the month: =--LEFT(F2,2) Or, you can quote the TEXT numbers in the SUMP formula: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000="1"),--(Sheet2!G2:G50000="2008"),D2:D50000) -- Biff Microsoft Excel MVP "Tasha" wrote in message ... I have been working for over an hour to try and figure out why I am not getting this formula right. Can someone please see if they can figure out what I'm doing wrong? Is it my formats maybe? I keep getting 0 or #VALUE! My data: A B C D E F G H id# date code qty amt month/day yr month C is formatted as text in F2, formula =right(b2,5) copied down in G2, formula =left(b2,4) copied down in H2, formula =left(f2,2) copied down the date is imported as text. I need to on another sheet get information summed for specified code, month, year, and sum column D if all that is true. So for Jan 2008 code 199000: =SUMPRODUCT(--(Sheet2!C2:C50000="199000"),--(Sheet2!H2:H50000=1),--(Sheet2!G2:G50000=2008),D2:D50000) Hope someone can help me, this is so maddening! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Problem | Excel Worksheet Functions | |||
sumproduct problem | Excel Worksheet Functions | |||
SUMPRODUCT Problem | New Users to Excel | |||
sumproduct problem? | Excel Discussion (Misc queries) | |||
Sumproduct Problem | Excel Worksheet Functions |