# 3 conditional formula help

#### kylee

##### Board Regular
Hi all,
I'm sooooo close to having all these spreadsheets automated - and it's all thanks to y'all! Thank you for all the help. I have one more question though. I got help yesterday with a formula and told the boss his spreadsheet is now automated, but then he asked what about the other one that has three age groups instead of two. I had to answer I'm working on it. lol

Employees can choose EE, ES, EC or EF - there are no blank answers and no exceptions to this option - ever. Dates of birth are entered and their age is calculated - which is where my question comes in.

Column: A=Calculated Age, B=Date of Birth, C=Option Chosen
West Man gave me the formula =SUMPRODUCT(--(A14:A125<55),--(C14:C125="ee")) - which will automatically fill in the subtotal boxes at the top of the spreadsheet - which is divided into 2 sections. Under age 55, & over 54 - the formula figures out which section fits then counts how many of each option there are and fills it in for me. PERFECTLY!!

But, bossman says that some carriers require three sections - 18-54, 55-59, 60+. So, how do I make the formula West Man gave me fit this new criteria?

Thanks again for all of your help!!
Ky

PS - I can't get the COLO thingie to work right, but in case y'all need to see what I'm trying to do ...

<SCRIPT language=JavaScript src="<a href=" popup.js" colo puremis sun www.interq.or.jp http:></SCRIPT><CENTER><TABLE" target="_blank">http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="13" bgColor="#0c266b"><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>Microsoft Excel - COPY APL Medlink II & III_ Master.xls</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" colSpan="13" bgColor="#d4d0c8"><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" colSpan="13" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value==SUM(G2:I2) selected>J2<OPTION value==TODAY()>B3<OPTION value==A3-B3>C3<OPTION value==SUM(G3:I3)>J3<OPTION value==SUM(G4:I4)>J4<OPTION value==SUM(G5:I5)>J5<OPTION value==G2+G3+G4+G5>G6<OPTION value==H2+H3+H4+H5>H6<OPTION value==I2+I3+I4+I5>I6<OPTION value==G6+H6+I6>L7<OPTION value='=IF(B14>0,DATEDIF(B14,\$B\$3,"Y"))'>A14<OPTION value==+D13+1>D14<OPTION value='=IF(B15>0,DATEDIF(B15,\$B\$3,"Y"))'>A15<OPTION value==+D14+1>D15<OPTION value='=IF(B16>0,DATEDIF(B16,\$B\$3,"Y"))'>A16<OPTION value==+D15+1>D16<OPTION value='=IF(B17>0,DATEDIF(B17,\$B\$3,"Y"))'>A17<OPTION value==+D16+1>D17<OPTION value='=IF(B18>0,DATEDIF(B18,\$B\$3,"Y"))'>A18<OPTION value==+D17+1>D18<OPTION value='=IF(B19>0,DATEDIF(B19,\$B\$3,"Y"))'>A19<OPTION value==+D18+1>D19<OPTION value='=IF(B20>0,DATEDIF(B20,\$B\$3,"Y"))'>A20<OPTION value==+D19+1>D20<OPTION value='=IF(B21>0,DATEDIF(B21,\$B\$3,"Y"))'>A21<OPTION value==+D20+1>D21<OPTION value='=IF(B22>0,DATEDIF(B22,\$B\$3,"Y"))'>A22<OPTION value='=IF(B23>0,DATEDIF(B23,\$B\$3,"Y"))'>A23<OPTION value==+D22+1>D23<OPTION value='=IF(B24>0,DATEDIF(B24,\$B\$3,"Y"))'>A24<OPTION value==+D23+1>D24<OPTION value='=IF(B25>0,DATEDIF(B25,\$B\$3,"Y"))'>A25<OPTION value==+D24+1>D25<OPTION value='=IF(B26>0,DATEDIF(B26,\$B\$3,"Y"))'>A26<OPTION value==+D25+1>D26<OPTION value='=IF(B27>0,DATEDIF(B27,\$B\$3,"Y"))'>A27<OPTION value==+D26+1>D27<OPTION value='=IF(B28>0,DATEDIF(B28,\$B\$3,"Y"))'>A28<OPTION value='=IF(B29>0,DATEDIF(B29,\$B\$3,"Y"))'>A29<OPTION value='=IF(B30>0,DATEDIF(B30,\$B\$3,"Y"))'>A30<OPTION value==+D29+1>D30<OPTION value='=IF(B31>0,DATEDIF(B31,\$B\$3,"Y"))'>A31<OPTION value==+D30+1>D31<OPTION value='=IF(B32>0,DATEDIF(B32,\$B\$3,"Y"))'>A32<OPTION value==+D31+1>D32<OPTION value='=IF(B33>0,DATEDIF(B33,\$B\$3,"Y"))'>A33<OPTION value==+D32+1>D33<OPTION value='=IF(B34>0,DATEDIF(B34,\$B\$3,"Y"))'>A34<OPTION value==+D33+1>D34<OPTION value==(\$A\$3-B35-\$C\$3)/365.25>A35<OPTION value==+D34+1>D35</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==SUM(G2:I2) name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align="middle" width="2%">
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
</CENTER>

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### SteveO59L

##### Well-known Member
Possibly something along the lines of

=SUMPRODUCT(--(A14:A125<55),--(C14:C125="ee")) for the first

=SUMPRODUCT(--(A14:A125<59),--(C14:C125="ee")) minus the result from the first

=SUMPRODUCT(--(A14:A125>58),--(C14:C125="ee")) for the third

#### Andrew Poulsom

##### MrExcel MVP
Is it?

=SUMPRODUCT(--(A14:A125>54),--(A14:A125<60),--(C14:C125="ee"))

#### kylee

##### Board Regular
Steve, thanks for the reply but -
all of the arguments need to be in the same formula.

The census is filled out;
the top of the page has a table of 5 rows, 3 columns: The columns are under 54, 55-59 and 60+, the rows are EE, ES, EC, EF; I need the appropriate cell to automatically come up with the result of every person in the census within the date range who chose which option.

Make more sense?

#### kylee

##### Board Regular
Is it?

=SUMPRODUCT(--(A14:A125>54),--(A14:A125<60),--(C14:C125="ee"))

Oooh, that looks great! Lemme try it and I'll let you know how it worked. Thanks!!

#### kylee

##### Board Regular
I can't even begin to tell y'all how wonderful this site is! Thanks so much - I'm sure I'll be back, but hopefully not for awhile. =)
~Ky

Replies
1
Views
107
Replies
1
Views
97
Replies
2
Views
168
Replies
10
Views
691
Replies
28
Views
191

1,186,676
Messages
5,959,150
Members
438,399
Latest member
jsparrow22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browserâ€™s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back