SUMPRODUCT Formula Too Long: can MATCH Codelist help?

[TomYC]

I have a SUMPRODUCT formula that is already long, and needs to get a bit longer, to cover a few more possibilities, but I soon reach the limit. What is the workaround, for a formula that is currently:
=SUMPRODUCT(('[CO tool 2010.xls]data'!$A$2:$A$8000>VALUE("1/31/2010"))*('[CO tool 2010.xls]data'!$A$2:$A$8000<VALUE("3/1/2010")*('[CO tool 2010.xls]data'!$K$2:$K$8000="1:1 MD")*('[CO tool 2010.xls]data'!$I$2:$I$8000=$A$5))*('[CO tool 2010.xls]data'!$N$2:$P$8000<>"")*(('[CO tool 2010.xls]data'!$B$2:$B$8000="3 Tower")+('[CO tool 2010.xls]data'!$B$2:$B$8000="8 Tower")+('[CO tool 2010.xls]data'!$B$2:$B$8000="PICU")+('[CO tool 2010.xls]data'!$B$2:$B$8000="CCU")))
As you can see, I wish there were a briefer way to reference network folders, but more immediately I need to add to the latter portion of the formula!!

 

[Gruuuu]

What exactly is the needed result? Can you define what you're trying to accomplish with the original formula, and what else you need it to accomplish?

 

[TomYC]

Pretty basic request: I am trying to get a count of records that fulfill the date range indicated in the first two terms of the SUMPRODUCT, that also fulfill a few other criteria, and then the final criterion, that the records in col. B contain one of the several values I have hardcoded ("3 Tower", "8 Tower", etc.)--it is that latter list of values to which I need a add a few, which take the formula beyond the pale.
If this can be accomplished by COUNTIF, then I'm all for it, but SUMPRODUCT has worked for me up till now.

 

[SkipVought]

Hi,

I see some syntax problems...
[tt]
=SUMPRODUCT
(
('[CO tool 2010.xls]data'!$A$2:$A$8000>DATEVALUE("1/31/2010"))*
('[CO tool 2010.xls]data'!$A$2:$A$8000<DATEVALUE("3/1/2010"))*
('[CO tool 2010.xls]data'!$K$2:$K$8000="1:1 MD")*
('[CO tool 2010.xls]data'!$I$2:$I$8000=$A$5)*
('[CO tool 2010.xls]data'!$N$2:$P$8000<>"")*
(
('[CO tool 2010.xls]data'!$B$2:$B$8000="3 Tower")+
('[CO tool 2010.xls]data'!$B$2:$B$8000="8 Tower")+
('[CO tool 2010.xls]data'!$B$2:$B$8000="PICU")+
('[CO tool 2010.xls]data'!$B$2:$B$8000="CCU")
)
)
[/tt]
Why not MS Query? faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

you could also replace the two date criteria with
[tt]
TEXT('[CO tool 2010.xls]data'!$A$2:$A$8000,"yyyymm")="201002"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

and use array constant in the second part ...
...*(('[CO tool 2010.xls]data'!$B$2:$B$8000={"3 Tower","8 Tower","PICU","CCU"})))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[TomYC]

Thanks to both of you; I'm still working this out--can you really use an array inside of a SUMPRODUCT expression? I haven't gotten it base #NA yet, but I won't give up!

 

[TomYC]

I am wondering if it is not possible to invoke two separate arrays inside this SUMPRODUCT?
As you can see, I already have one above, in the term:
('[CO tool 2010.xls]data'!$N$2:$P$8000<>"")
so when I combine it with one of your suggestions:
*('[CO tool 2010.xls]data'!$B$2:$B$8000={"3 Tower","8 Tower","PICU","CCU"})
I get the #N/A error. And if I temporarily modify either of these terms down to a one-dimensional array, either:
$N$2:$N$8000<>""
or
$B$2:$B$8000={"3 Tower"}
Then I get a real number (not the full total that I want).
Can SUMPRODUCT not handle this?

 

[TomYC]

I'm also having problems with the date range--
I like Skip's suggestion above:
TEXT('[CO tool 2010.xls]data'!$A$2:$A$8000,"yyyymm")="201002"
As I am working with dates such as 12/31/09 and 2/13/10, I have been experimenting to get TEXT() to work. "mmyy" works with 1209, but I can't get anything to work with my 2010 dates in the form of "myy" etc. Surely there is some universal method here, or does the lack of leading zeroes on months mess it up?

 

[SkipVought]

I have moved columns and paired down the range, but the formula seems to work...
[tt]
=SUMPRODUCT((TEXT($A$2:$A$9,"yyyymm")="201002")*($C$2:$C$9="1:1 MD")*($D$2:$D$9=$A$5)*($E$2:$E$9<>"")*($B$2:$B$9={"3 Tower","8 Tower","PICU","CCU"}))
[/tt]
With regard to dates, I would avoid doing ANY comparisons in any order other than YMD. Yes, = would work, but when you get to comparing > or <, then MDY or DMY, you WILL get incorrect results!!!

In most cases, I'd be working directly with date values and not strings, but this is a "shortcut" that works for one year-month's worth of stuff. Its like choosing the correct hammer for the job (I have 8).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip seems to have lost the need to reference a different workbook. If this is possible then wouldn't DCOUNT or one of the other database functions be an option?
This will count all records that meet the criteria in the criteria range that you specify.

Could your formula be in the CO tool 2010.xls workbook and you merely pull the result into your other workbook?

Gavin

 

[SkipVought]

Shouldn't make a difference it the WB is open.

I might use MS Query to return a value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

I am wondering if it is not possible to invoke two separate arrays inside this SUMPRODUCT?
As you can see, I already have one above, in the term:
('[CO tool 2010.xls]data'!$N$2:$P$8000<>"")
so when I combine it with one of your suggestions:
*('[CO tool 2010.xls]data'!$B$2:$B$8000={"3 Tower","8 Tower","PICU","CCU"})
I get the #N/A error. And if I temporarily modify either of these terms down to a one-dimensional array, either:
$N$2:$N$8000<>""
or
$B$2:$B$8000={"3 Tower"}
Then I get a real number (not the full total that I want).
Can SUMPRODUCT not handle this?

It'll do 1 column * multiple array
or multicolumn * single item
or multicolumn * multiple array of same size
but it won't do multicolumn * multiple array of different size. So this would work:

=SUMPRODUCT(('[CO tool 2010.xls]data'!$N$2:$P$8000<>"")*('[CO tool 2010.xls]data'!$B$2:$B$8000={"3 Tower","8 Tower","PICU"}))

as it's 3 columns * 3 way test array.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

... following on from my previous post ... note that the logic of the result may not be what you want for the formula I posted, as it will test "3 Tower" against column N, "8 Tower" against column O and "PICU" against column P.

What is it that you want to test in columns N? Is that they are all not ""?

You may be better off having an intermediate column calc in '[CO tool 2010.xls]data' to give a single column to use in your sumproduct calculations.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

... following on from my follow on ... the test will not actually be of "3 Tower" against column N, it will be the multiplication of the results of the test of "3 Tower" ( with column B ) with column N, etc etc.

I hope that all makes sense.

Cheers, Glenn.

Beauty is in the eye of the beerholder.