# If then Countif

#### p86c

##### New Member
Hi everyone - hoping you might help with this - it seems like an easy thing to propose but I have spent most of the morning googling and haven't got what I am hoping to find yet.

I have a spreadsheet with a column of student names and rows of subjects into which grades are entered.

At the end of each row I have used a Countif to total the number of each type of grade that each student name has achieved. - this works fine.

I have added a column to the right of the Student Names called "Remove" and I would like to use it in such a way that if I enter a character eg an "0" into a cell beside a name that I can stop the Countif from totalling the grades of each type, but if the character in the cell is a "1" then the formula works as it did.

I haven't had any success doing this...
but I have learnt about filtering - which still adds up even hidden rows.
I also thought about multiple criteria Countifs - but the criteria seems to require a range, rather than the single cell check I want to use.
I have got tied up in SUMPRODUCT knots to be honest.

the working formula that just adds up, for example C grades is
=COUNTIF(F13:AF13,"C")

I am trying to get that formula to return a result only when C13 (my "Remove" column) has a !1" in it - otherwise it should return a zero count.

Hoping this might be something you can advise on?
p86c

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### gaz_chops

##### Well-known Member
Welcome to the forum, maybe

=if(C13=1,COUNTIF(F13:AF13,"C"),0)

#### p86c

##### New Member
That's it! Thankyou very much gaz_chops!

so.... just so I understand what this function means

the COUNTIF only does its job if C13 is 1 and if it isn't it returns the value 0 as shown after the comma....

is that it?

#### gaz_chops

##### Well-known Member
You're welcome and yes, the if statement is saying if C13 meets the criteria then do the countif, otherwise return zero.

#### arthurbr

##### Well-known Member

A shorter version for the fun of it =(C13=1)*COUNTIF(F13:AF13,"C")

#### p86c

##### New Member
You're welcome and yes, the if statement is saying if C13 meets the criteria then do the countif, otherwise return zero.
Thanks again - much appreciated.

#### p86c

##### New Member
A shorter version for the fun of it =(C13=1)*COUNTIF(F13:AF13,"C")
Thanks for this too - I did try it for the fun of it too!

This has all made my morning worthwhile.

Replies
7
Views
44
Replies
2
Views
38
Replies
1
Views
125
Replies
1
Views
154
Replies
3
Views
149