Mixing text and alpha in sumproduct arrays
 

Just learning how to use Sumproduct to search for values. Looks like a very
powerful little function. But I keep getting a #VALUE! error when I have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release date, so
it looks something like this: (hopefully the columns will line up in your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has text
scattered within the data. I found that if I limit the Sumproduct function
above to the non-text areas it works fine, but as soon as I include an area
in the array that has text, or type text into a cell in a working array, I
get the #VALUE! error.

Help please!
TIA

Mixing text and alpha in sumproduct arrays
 

Try this array formula** :

=SUM(IF(E70:T99=12,F70:U99))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Just learning how to use Sumproduct to search for values. Looks like a
very
powerful little function. But I keep getting a #VALUE! error when I have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release date,
so
it looks something like this: (hopefully the columns will line up in your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has text
scattered within the data. I found that if I limit the Sumproduct
function
above to the non-text areas it works fine, but as soon as I include an
area
in the array that has text, or type text into a cell in a working array, I
get the #VALUE! error.

Help please!
TIA

Mixing text and alpha in sumproduct arrays
 

That works, which led me to also try:
=SUMIF(E5:T99,"=12",F5:U99)
which works as well.
So sumproduct does not allow mixing text and alpha in the array but sumif
does?

And in trying to understand the "Why", what is the benefit of using an array
formula. Is there a good refernece doc on array formulas anywhere? I've
used them before but only for Transposing.
Thanks
ed

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(E70:T99=12,F70:U99))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Just learning how to use Sumproduct to search for values. Looks like a
very
powerful little function. But I keep getting a #VALUE! error when I have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release date,
so
it looks something like this: (hopefully the columns will line up in your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has text
scattered within the data. I found that if I limit the Sumproduct
function
above to the non-text areas it works fine, but as soon as I include an
area
in the array that has text, or type text into a cell in a working array, I
get the #VALUE! error.

Help please!
TIA

Mixing text and alpha in sumproduct arrays
 

=SUMIF(E5:T99,"=12",F5:U99)

Yeah, I didn't even think of that. You can reduce it to:

=SUMIF(E5:T99,12,F5:U99)

So sumproduct does not allow mixing text and alpha in the array

It does but it depends on what form of SUMPRODUCT you use. The form you
tried won't work.

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Is there a good refernece doc on array formulas anywhere?

See this for an explanation of array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
That works, which led me to also try:
=SUMIF(E5:T99,"=12",F5:U99)
which works as well.
So sumproduct does not allow mixing text and alpha in the array but sumif
does?

And in trying to understand the "Why", what is the benefit of using an
array
formula. Is there a good refernece doc on array formulas anywhere? I've
used them before but only for Transposing.
Thanks
ed

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(E70:T99=12,F70:U99))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Just learning how to use Sumproduct to search for values. Looks like a
very
powerful little function. But I keep getting a #VALUE! error when I
have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release
date,
so
it looks something like this: (hopefully the columns will line up in
your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has text
scattered within the data. I found that if I limit the Sumproduct
function
above to the non-text areas it works fine, but as soon as I include an
area
in the array that has text, or type text into a cell in a working
array, I
get the #VALUE! error.

Help please!
TIA

Mixing text and alpha in sumproduct arrays
 

Excellent, thanks for the help!

"T. Valko" wrote:

=SUMIF(E5:T99,"=12",F5:U99)

Yeah, I didn't even think of that. You can reduce it to:

=SUMIF(E5:T99,12,F5:U99)

So sumproduct does not allow mixing text and alpha in the array

It does but it depends on what form of SUMPRODUCT you use. The form you
tried won't work.

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Is there a good refernece doc on array formulas anywhere?

See this for an explanation of array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
That works, which led me to also try:
=SUMIF(E5:T99,"=12",F5:U99)
which works as well.
So sumproduct does not allow mixing text and alpha in the array but sumif
does?

And in trying to understand the "Why", what is the benefit of using an
array
formula. Is there a good refernece doc on array formulas anywhere? I've
used them before but only for Transposing.
Thanks
ed

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(E70:T99=12,F70:U99))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Just learning how to use Sumproduct to search for values. Looks like a
very
powerful little function. But I keep getting a #VALUE! error when I
have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release
date,
so
it looks something like this: (hopefully the columns will line up in
your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has text
scattered within the data. I found that if I limit the Sumproduct
function
above to the non-text areas it works fine, but as soon as I include an
area
in the array that has text, or type text into a cell in a working
array, I
get the #VALUE! error.

Help please!
TIA

Mixing text and alpha in sumproduct arrays
 

You're welcome!

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Excellent, thanks for the help!

"T. Valko" wrote:

=SUMIF(E5:T99,"=12",F5:U99)

Yeah, I didn't even think of that. You can reduce it to:

=SUMIF(E5:T99,12,F5:U99)

So sumproduct does not allow mixing text and alpha in the array

It does but it depends on what form of SUMPRODUCT you use. The form you
tried won't work.

See this for a comprehensive analysis of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Is there a good refernece doc on array formulas anywhere?

See this for an explanation of array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
That works, which led me to also try:
=SUMIF(E5:T99,"=12",F5:U99)
which works as well.
So sumproduct does not allow mixing text and alpha in the array but
sumif
does?

And in trying to understand the "Why", what is the benefit of using an
array
formula. Is there a good refernece doc on array formulas anywhere?
I've
used them before but only for Transposing.
Thanks
ed

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(E70:T99=12,F70:U99))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"expect_ed" wrote in message
...
Just learning how to use Sumproduct to search for values. Looks
like a
very
powerful little function. But I keep getting a #VALUE! error when I
have
text in the array. Here is my formula:

=SUMPRODUCT((E70:T99=12)*(F70:U99))

If there is not a direct solution, here is my more general goal.
I have a large grid with Work Orders and Hrs in columns by release
date,
so
it looks something like this: (hopefully the columns will line up in
your
view)

Oct Nov Dec
WO Req Dev QA WO Req Dev QA WO Req Dev QA

2 40 8 44
4 33 9
80
5 45 3
24

And in another tab I would like the data to come out like this:

WO Req Dev QA
2 40
3 24
4 33
5 45
6
7
8 44
9 80

Unfortunately the actual sheet is a bit more complicated and has
text
scattered within the data. I found that if I limit the Sumproduct
function
above to the non-text areas it works fine, but as soon as I include
an
area
in the array that has text, or type text into a cell in a working
array, I
get the #VALUE! error.

Help please!
TIA