Using if then statement that doesn't apply cell-to-cell but for the whole column

[JustJen]

Hello,

I am using Excel 2007 on Windows 7... and have tried using every formula I can think of (or google has helped me find) to solve my problem without any luck. Any guidance would be greatly appreciated.

A sample of my data:
Month [TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Name[/TD]
[TD]Date Arrived[/TD]
[TD]Primary Reason for Coming?[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]John Smith[/TD]
[TD]29/1/2012[/TD]
[TD]Friends[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]Jane Smith[/TD]
[TD]31/1/2012[/TD]
[TD]Outreach[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]Mike Smith[/TD]
[TD]2/2/2012[/TD]
[TD]Friends[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to the data entry person to only have to enter in the above data and the summary information (i.e. monthly totals, % of new participants who identified outreach as their primary reason for coming etc per month) will come up automatically in a protected cell.

I want to calculate the % of new participants who identified friends as their primary reason for coming to our organization. I want, for each row, if the cell in column A=January then go to column E and if this corresponding cell (i.e. If A2 = January then go to E2) =Friends count it. I want this if then statement to go down the entire column until it gets to a blank cell... so that I end up with an equation that will let me do = (all new participants in January who said friends)/(total new participants in January). I'm hoping whatever wonderful formula allows me to do this can be done automatically each time someone types into the excel sheet.

I can create a formula to calculate the total participants per month, but not able to figure out how to calculate the numerator.

Any ideas? I am totally lost at the moment so any direction would be appreciated.

Thank you so much for your help on this!
Jen

 

[Jerry Sullivan]

Hi Jen and Welcome to the Board,

If you have xl2007 or later, you can use the COUNTIFS function to get the numerator as shown in the example below.
If you have an earlier version of Excel, you could use a SUMPRODUCT formula in a similar way.

Excel Workbook
	A	B	C	D	E	F	G
1	Month	Name	Date Arrived	Primary Reason for Coming?		Criteria
2	January	John Smith	29/1/2012	Friends		Month	January
3	January	Jane Smith	31/1/2012	Outreach		Primary Reason	Friends
4	February	Mike Smith	2/2/2012	Friends		Percent Result	50%
Sheet

 

[JustJen]

Hey,

That worked exactly as I needed it to.
Thank you so much for your assistance!!

Amazing

Have a great day,
Jen

Hi Jen and Welcome to the Board,

If you have xl2007 or later, you can use the COUNTIFS function to get the numerator as shown in the example below.
If you have an earlier version of Excel, you could use a SUMPRODUCT formula in a similar way.

*	A	B	C	D	E	F	G
*	Criteria	*
*	Month	January
*	Primary Reason	Friends
*	Percent Result

<colgroup><col style="width:30px; "><col style="width:70px;"><col style="width:82px;"><col style="width:90px;"><col style="width:186px;"><col style="width:64px;"><col style="width:106px;"><col style="width:70px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: left"]Month[/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]Date Arrived[/TD]
[TD="align: left"]Primary Reason for Coming?[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]January[/TD]
[TD="align: left"]John Smith[/TD]
[TD="align: left"]29/1/2012[/TD]
[TD="align: left"]Friends[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]January[/TD]
[TD="align: left"]Jane Smith[/TD]
[TD="align: left"]31/1/2012[/TD]
[TD="align: left"]Outreach[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]February[/TD]
[TD="align: left"]Mike Smith[/TD]
[TD="align: left"]2/2/2012[/TD]
[TD="align: left"]Friends[/TD]

[TD="align: center"]50%[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G4 =COUNTIFS(A$2:A$1000,$G$2,D$2:D$1000,$G$3)/COUNTIF(A$2:A$1000,G2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4