SUMPRODUCT giving #VALUE! error 2

[Loomah]

Hi all
I'm using sumproduct to find the total number of people who meet certain criteria in a pay model.

The following works perfectly

=SUMPRODUCT((Band=A4)*(Range=B4)*(FT_Sal>=B19)*(FT_Sal<=B20))

where Band, Range & FT_Sal are all named ranges which are (currently) 1 column by 500 rows, 1-500.

However when I try to calculate the full time equivalents using the following (where FTE is also a named range 1 col by 500 rows) I get #VALUE!

=SUMPRODUCT((Band=A4)*(Range=B4)*(FT_Sal>=B19)*(FT_Sal<=B20)*(FTE))

the range FTE contains numbers >0 to <=1 used for calculating workforce with part time staff.

I've checked (and re checked) obvious things like the size of ranges etc. I've also tried combining the first 2 arguments ie
=SUMPRODUCT((Band&Range=A4&B4)*(FT_Sal>=B19)*(FT_Sal<=B20)
works to give me a count but
=SUMPRODUCT((Band&Range=A4&B4)*(FT_Sal>=B19)*(FT_Sal<=B20)*(FTE))
doesn't!!

I've also tried changing the length of the ranges and checking formatting of cells in the FTE range but still no joy!

Any suggestions, I feel like I'm missing something really obvious?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

Check for a non-numeric value in the FTE range. Try using AutoFilter to spot.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[KenWright]

Does this work:-

=SUMPRODUCT(--(Band=A4),--(Range=B4),--(FT_Sal>=B19),--(FT_Sal<=B20),(FTE))

If so then you likely have text somewhere in your data, or maybe headers or maybe a stray blank etc

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

are your headers included in your named ranges ??

If you try and do a SUM as opposed to a COUNT in sumproduct, you cannot include non numerics like headers...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Ensure your ranges are the same size as well, else you could receive that error.

-----------
Regards,
Zack Barresse

 

[Loomah]

Skip
Yeah, checked that - I have utilities for cleaning up data but apart from that I even tried changing the named ranges to only 5 rows so I could check that manually!

Ken
I tried
=SUMPRODUCT(--(Band=A4),--(Range=B4),--(FT_Sal>=B19),--(FT_Sal<=B20),--(FTE))
which doesn't work but what's the difference between that and what you've posted (I know visually I have an extra "--"!!)
Your suggestion seems to work (as if I doubted it!!!) but will test again tomorrow (I'm at home watching World Cup now, using an older version of the model to test all these suggestions!)

xlbo
Yes, ranges include headers and, in the case of the newer version, they also include some blank rows which I need to get rid of. and I think you kind of partially answer my question to Ken!

firefytr
Thanks for your input. That was, however, the first thing I checked and re checked!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Actually, as a slight aside, is there a definitive site highlighting and explaining the different ways of using sumproduct and what the different syntaxes (such a word?) are?

I know Ken has done a load of stuff on this and posted a list of examples, which I am sure I bookmarked somewhere but I can't find it!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

basically:
don't include headers in ranges
ensure there are no errors in any of the data
make sure all ranges are the same depth
ensure consistent data types

that's about it really - kinda like databases - fields must have consistent data types with no errors and you only calculate using the data - not the headers.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Bob Phillips has a great white paper on SUMPRODUCT, here...

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

-----------
Regards,
Zack Barresse

 

[Loomah]

Thanks Zack, I knew I'd seen Ken's name attached to SUMPRODUCT somewhere in the past!!

Just a quick glance through that site shows I was only one key stroke from getting it working in the first place....

=SUMPRODUCT((Band=A4)*(Range=B4)*(FT_Sal>=B19)*(FT_Sal<=B20),(FTE))

!!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[firefytr]

Ah-ha! Isn't that always the case, where it comes down to one pesky character? LOL!

Glad you got it working.

-----------
Regards,
Zack Barresse

 

[Loomah]

OK, this is sorted so thanks for all the input. However I just had to share this, even at the risk of humiliating myself.

I got to work this morning armed with a little more knowledge (quite a bit really) about SUMPRODUCT than I had yesterday and smartly changed my formula.

The error went but the result was still wrong! So I manually filtered out all the data that met the relevant criteria. Only 2 rows relevant!! So made sure everything was clean and changed named ranges to exclude headings etc etc.

With the 2 FTE rows having value of 1.0 & 1.0 my result was 2 - OK!!
With 2 FTE rows having values of 0.5 & 0.5 result = 1 - still OK!
With the 2 rows having one val of 1.0 & the other 0.5 my result was 2.

I pulled my hair out trying to think what could still be wrong before it dawned on me that it might help (just a little) if I changed the format of the output!

Sometimes I think I know a little bit about Excel and other times I think I'd be better off cleaning windows or sweeping streets (no disrespect intended)!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------