Problem with SUMPRODUCT

[Tasha]

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),D250000)

Hope someone can help me, this is so maddening!

 

[T. Valko]

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"),D250000)

 

[Pete_UK]

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"),D250000)

Hope this helps.

Pete

 

[Tasha]

=SUMPRODUCT((erlvltrnd!C2:C35000="199000"),--(erlvltrnd!H2:H35000="1"),--(erlvltrnd!G2:G35000="2008"),D235000)
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?

 

[Tasha]

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.

 

[Tasha]

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]

OK, good deal. Thanks for the feedback!