Count If Then Statement

Microsoft Excel 2007
October 28, 2009 at 11:54:25
Specs: Windows XP
I am working on a complex free/reduced school lunch spreadsheet. I have it set up that when I enter "1" for each student on that specific day of the month, it charges the meal and then gives me the balance and the total that ate that day for each month. What I need to do is create a way that when i enter the "1" for each student on that day of the month it breaks down the F/N/R (free, non, and reduced). I have the cells already coded as follows " Column A = Student Name, Column C=(F,N,R) Column D=IF(C="N",2.5,IF(C="R",0.4,IF(C="F",0))), Column D is where i place the 1 (this is repeated for the entire month) and then the complex formula (posted below) calculates the total charges minus the deposits for the student. So what i am looking for is a way to count if the student is 1 and is F and if the student is 1 and the student is R and if the student is 1 and the student is N totaled over the entire range of one row.

This is the formula I used to calculate the daily count, each work book is broken up by month and the students are on colum A.
A clean copy with fake students is available on request to help sort out this formula

See More: Count If Then Statement

Report •

October 28, 2009 at 12:48:24
Well, I'm sufficiently confused.

You said: Column D=IF(C="N",2.5,IF(C="R",0.4,IF(C="F",0)))

Then you said: Column D is where i place the 1

1 - How can you have a formula and place a 1 in the same cell?

2 - What does the "complex formula" that you posted have to do with counting the 1's and F/N/R's?

3 - It sounds like you want to count the 1-F, 1-N and 1-R combinations, but I don't understand what you mean by totaled over the entire range of one row If the 1 is in D and the letter is in C, how can there be more than one combination in any given row?

Report •

October 28, 2009 at 13:05:41
Woops, forgive me, my brain is about fried on this :)

OK I have a month set up on each work book. The students are listed on the left hand side, the days of the week are along the top. each day has three cells associated with it. one is for F, N, or R, the second cell is the cost and has this nifty formula =IF(H4="P",2.5,IF(H4="R",0.4,IF(H4="F",0))), the third cell is left empty unless a student actually eats. What I am looking for is a way to count:
F-1, N-1, R-1 for each "day". I think i ment to say Column and not row.

Report •

October 28, 2009 at 13:23:30

I need some clarification.

You have the amount per meal in Column D based on the code F, N or R in column C, but you say that you put 1 in column D, please clarify.

Which columns contain the dates for the month

Which column(s) contains the deposit.

You have a lot of pairs of columns that are multiplied together :
(F4*G4)+(I4*J4)+(L4*M4) etc.
What do these pairs of cells contain, and what is in the 'missing' column between each pair (H & K in the sample above).

Here are some sample formulas
If the student on row 2 has name in A2, F/N/R in C2 and a value (0/0.4/2.5) in D2 and then 1 or blank in say 20 daily cells from F2 to Y2,
the following formula in Z2 will count the number of meals taken:

and the total cost for that student for the 20 days at the rate specified in D2: is calculated by this formula in AA2:

A further formula subtracting the total cost from the deposit will yield the balance.

To get monthly statistics:
In cells AD2 to AD4 enter F, N and R
In cells AE2 to AE4 are formulas to calculate the totals for each meal charge.

My sample spreadsheet had 1s or blanks for 12 students in rows 2 to 13

1	Type	#
2	F	54
3	N	50
4	R	52
5	Total	156

In cell AE2 enter this formula:

Then place the cursor inside the formula in the formula bar and press Ctrl+Shift+Enter keys together.
If you get this right the formula will be surrounded by curly braces:
Note: you cannot enter the curly braces from the keyboard.
Note the $ signs in the formula

Then drag the formula down two further rows.

Add Total underneath column AE

The formula in curly braces, entered with Ctrl+Shift+Enter is known as an array formula.


Report •

Related Solutions

October 28, 2009 at 13:49:49
With this data:

        C        D
1       F        1
2       R        1
3       N        1
4       F        1
5       N        1

This Array Formula will return 2:


You obtain the brackets by using Ctrl-Shift-Enter to enter the formula.

You must use that combination of keys everytime you edit the formula.


Report •

October 28, 2009 at 14:16:50

Following up on the information you provided in response to DerbyDad03's question,

It appears that you can simplify this substantially.

As each student either takes or does not take a meal each day and as they can only take one type of meal F, N or R, then just have one column per day.

Enter F, N or R if the student takes a meal and leave blank if no meal.

At the end of each row have three columns to count how many of each meal type the student took.
20 days in columns F to Y
Column Z for F meals, column AA for N meals and column AB for R meals
For row 2:
Z2 is =COUNTIF($F2:$Y2,"F")
AA2 is =COUNTIF($F2:$Y2,"N")
AB2 is =COUNTIF($F2:$Y2,"R")

AC2 is the total cost for this student
AC2 is =(AA2*0.4)+(AB2*2.5)

Drag all the formulas down the required number of rows.

At the bottom of these columns you can now total the numbers of each type of meal taken, and the total money collected..

To make sure there are no typos when entering F, N or R
1. Create a list of four cells , containing F, N, R and the last one empty.
2. Select all the cells under the dates,
3. Select Data, Data Tools, Data Validation
In the dialog box that opens select List in the Allow box, and enter the address of the four cell list in the Source box.
Click OK

Now when you go to enter F, N or R in a cell you get a drop down list in each cell. You cannot enter anything other than those letters or leave the cell blank.
You can still enter F, N or R from the keyboard (you don't have to use the drop down) but if you hit M instead of N for example, you get a warning and have to enter an allowed value.

COUNTIF() is not case sensitive so n or N are both treated as the same.


Report •

October 28, 2009 at 14:47:38

I have posted an example of two months (one workbook with hiden cells and one with all exposed cells).

I did the {=SUM((G4:G83=1)*(E4:E83="F"))} with cntrl shift enter and it worked :) THANK YOU!!!! now all i need to do is populate the rest of the work books (months) and its all done :)

Thank you all for your input!! It helped!!!!!!


Report •

Ask Question