Mixing text and alpha in sumproduct arrays

[expect_ed]

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

 

[T. Valko]

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)

 

[expect_ed]

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]

=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

 

[expect_ed]

Excellent, thanks for the help!

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

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


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


See this for an explanation of array formulas:

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Excellent, thanks for the help!