Array Formulae

[PearlGI]

Hi, I've been using array formulae (formulae entered with [Ctrl]+[Shift]+[Enter]) for many years now, but can not work out how to get them to work (in just one formulae) if the value you wish to compare the array to is not a single value.

For instance, if I wish to sum the value of 'Dog' from a range containing animal weights the formulae would be:

{=SUM(IF(AnimalNameRange="Dog",AnimalWeightRange,0))}

Now what if you need the combined weight of both 'Dogs' and 'Cats'? The simple answer would be use 2 formulae and add the results together.

However, if the situation becomes more complex if you need to use many options, i.e. Dogs, Cats, Fish, Bird, Horse, etc, etc.

Surely there must be a way to combine this within a single array formulae, for instance something along the lines of:

{=SUM(IF(AnimalNameRange={ArrayToFind},AnimalWeightRange,0))}
or
{=SUM(IF(AnimalNameRange=OR(ArrayToFind),AnimalWeightRange,0))}

but try as I might nothing seems to work. Can anyone get this idea to work?

 

[Brianwarnock]

I haven't looked at it but would examine sumproduct, see the links given in one of the threads on here.

Brian

 

[PearlGI]

The SUMPRODUCT examples that I've seen seem to deal only which the boolean 'AND' logic with multiple arrays. What I need is the boolean 'OR' within a single array.

SUMPRODUCT also needs the arrays to be the same size.

 

[PearlGI]

Ah, I've found the solution.

Whilst trawling through the posts on SUMPRODUCT I found a link to this site http://xldynamic.com/source/xld.SUMPRODUCT.html (suggested by qafself)

Very interesting reading, I didn't realise how versatile the SUMPRODUCT was!

Anyway, this inadvertently lead me to discover the flaw in my approach. Excel doesn't like criteria expressed in a column and will only deal with rows, therefore the TRANSPOSE function has to be used to convert.
Following this back to my original question (hoping it may be of use to someone else), this becomes:

{=SUM(IF(AnimalNameRange=TRANSPOSE(ArrayToFind),AnimalWeightRange,0))}

This still uses array formulae, but at least I've discovered how deep the SUMPRODUCT world extends..... thanks for your insight Brian

 

[Brianwarnock]

Happy to have assisted, especially somebody willing and able to develop the ideas themself. Thanks for posting back.

Brian