# Excel functions

silliw 23:06 07 Dec 2006
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 2006

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 2006

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 2006

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 2006

(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.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?