# Excel functions

silliw 23:06 07 Dec 06
Locked

I want to write a function which does an "if" and an "and" to look at two cells and dependant upon their contents return a choice of two characters.
I am trying to sort a list containing DOB and sex to return a count of all males and females between the age range of 20-30.

thanks

VoG II 05:41 08 Dec 06

We need a better description of your worksheet layout - i.e. exactly what you have and where - and what you are trying to achieve. From your brief outline I would say that you need a SUMPRODUCT, not a combination of IF and AND.

VoG II 08:30 08 Dec 06

For example with F or M in A2:A11 and ages as integers in B2:B11

=SUMPRODUCT(--(A2:A11="F"),--(B2:B11>19),--(B2:B11<31))

will returm the number of females aged 20-30.

silliw 16:53 08 Dec 06

Thanks Vog - have never seen a formula with dashes in it before but it works great. I am doing a census and was trying to identify how many individuals in a list of 205 were male or female and what age they were between the age ranges of 8-16. I had done a number of ifand statements to achieve this but yours works far better.

VoG II 17:03 08 Dec 06

(A2:A11="F") returns an array of True or False values, similarly (B2:B11>19) and (B2:B11<31) return arrays of Boolean values. The -- is used to coerce these from True or False to 1 or 0 respectively. SUMPRODUCT can then count the instances in each of the three arrays where 1 occurs in the same place. An alternative is

=SUMPRODUCT((A2:A11="F")*(B2:B11>19)*(B2:B11<31))

but this is somewhat more 'expensive' in terms of computation. This is only really noticeable when dealing with large arrays.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac