If then Countif

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Welcome to the forum, maybe

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

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Dec 8, 2006
Messages
2,161
Office Version
  1. 2010

ADVERTISEMENT

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

p86c

New Member
Joined
Jun 13, 2020
Messages
16
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,479
Members
412,596
Latest member
nickthebizz
Top