Non-consecutive countif divided by non-consecutive counta

mtaylor

Board Regular
Joined
May 1, 2013
Messages
51
Platform
  1. Windows
  2. MacOS
Hi there,

Ok so...

The 14th, 15th and 17th I would like counting the number of "P"'s there are, divided by the number of cells containing "something". I have this set up nicely for previous weeks, however I've become stuck now that the days I want are non-consecutive.

Previous weeks I use the formula: =IF(COUNTA(BN3:BR3),(COUNTIF(BN3:BR3,"P")+COUNTIF(BN3:BR3,"L"))/COUNTA(BN3:BR3)*100,"")

Now I'm looking at something along the lines of: =IF(COUNTA(BT3:BU3,BW3),(COUNTIF(BT3:BU3,BW3,"P")+COUNTIF(BT3:BU3,BW3,"L"))/COUNTA(BT3:BU3,BW3)*100,"")

It would appear they don't like the comma before the "P" and "L"

The cell under Week 13 % is where the formula should be going.


HSHS
14/0615/0616/0617/0618/06Week 13 %
PPUAPUA#N/A


Thanks in advance folks
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows
What is the reason behind the choice of dates? From your example, I see

=IFERROR(SUM(COUNTIFS(BN3:BR3,{"P","L"},BN1:BR1,"<>HS"))/COUNTA(BN3:BR3),"")
 

mtaylor

Board Regular
Joined
May 1, 2013
Messages
51
Platform
  1. Windows
  2. MacOS
What is the reason behind the choice of dates? From your example, I see

=IFERROR(SUM(COUNTIFS(BN3:BR3,{"P","L"},BN1:BR1,"<>HS"))/COUNTA(BN3:BR3),"")

Just because some days students are required for exams, whereas other days are just self study at home.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows
whereas other days are just self study at home.
I assume that this is identified by "HS" in the top row?

Try this formula
=IFERROR(SUM(COUNTIFS(BN3:BR3,{"P","L"},BN1:BR1,"<>HS"))/COUNTIFS(BN3:BR3,"<>",BN1:BR1,"<>HS"),"")
 

mtaylor

Board Regular
Joined
May 1, 2013
Messages
51
Platform
  1. Windows
  2. MacOS
I assume that this is identified by "HS" in the top row?

Try this formula
=IFERROR(SUM(COUNTIFS(BN3:BR3,{"P","L"},BN1:BR1,"<>HS"))/COUNTIFS(BN3:BR3,"<>",BN1:BR1,"<>HS"),"")

Correct - the HS stands for Home Study. I'll have a look.

Thank you
 

mtaylor

Board Regular
Joined
May 1, 2013
Messages
51
Platform
  1. Windows
  2. MacOS
I assume that this is identified by "HS" in the top row?

Try this formula
=IFERROR(SUM(COUNTIFS(BN3:BR3,{"P","L"},BN1:BR1,"<>HS"))/COUNTIFS(BN3:BR3,"<>",BN1:BR1,"<>HS"),"")

You're a hero.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,178
Messages
5,576,552
Members
412,731
Latest member
yaseen381
Top