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!

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!

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!

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!

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!

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!

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!