Ignore blanks when using COUNTIFS

[geepy]

In Column A I have a list of codes which start with 'SECU'. Then in Column B, I have various information which I only want to check if Column A says SECU0003 (not, for example, SECU0006). So, I want Excel to check Column A and if the cell in that column says SECU0003, I then want Excel to check Column B and count how many cells contain the letter "P" (and no other letters either side of it). Once I have that figure, I want to divide that figure by the sum of all the SECU0003 cells which have any of 5 letters (including 'P'), and then multiply by 100 to get a percentage showing what percentage of all the cells show 'P'. I've had a go with the formula below but in the second COUNTIF formula, I know I can't add the letters with '+' in that part of the formula, but I don't know how to do it. I'm also not sure if I've written the formula in a way that will prevent blank cells from affecting the result.




=(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P"+"L"+"D"+"E"+"A")))*100


Please help, I've been going around in circles for hours

 

[Fluff]

How about
=(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/(COUNTIFS(C5:C85,"SECU0003")))*100

 

[geepy]

How about
=(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/(COUNTIFS(C5:C85,"SECU0003")))*100

Thank you, but the SECU0003 part for formula in column A is just to make sure that I'm working with the correct cells in the adjacent column B. The cells in column B contain one of five letters (one of these five letters is 'P'). In column B, there are also some cells which contain 'No Session', and some blank cells. I want to calculate what percentage of the cells containing letters are 'P' (ignoring any 'No Session' cells in the column, and making sure that blank cells don't affect the result).

Any ideas?

 

[Fluff]

In that case how about
=(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/(SUM(COUNTIFS(C5:C85,"SECU0003",E5:E85,{"P","L","D","E","A"}))))*100

 

[Marcelo Branco]

Maybe...

=COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/COUNTIFS(C5:C85,"SECU0003",E5:E85,"?")*100

M.

 

[geepy]

In that case how about
=(COUNTIFS(C5:C85,"SECU0003",E5:E85,"P")/(SUM(COUNTIFS(C5:C85,"SECU0003",E5:E85,{"P","L","D","E","A"}))))*100

Thanks, yes, I think that's it - the result in the E column is correct. However, when I drag the result to the right to do the same calculation for column F, I get the #DIV/0! error. I tried making the C5:C85 references absolute in the formula, because they should be, but I still get the same error. The only relative references in the formula are the E5:E85 ones, so I can't figure out why I'm getting the error??

 

[Fluff]

This works for me
=(COUNTIFS($C$5:$C$85,"SECU0003",E5:E85,"P")/(SUM(COUNTIFS($C$5:$C$85,"SECU0003",E5:E85,{"P","L","D","E","A"}))))*100

 

[geepy]

This works for me
=(COUNTIFS($C$5:$C$85,"SECU0003",E5:E85,"P")/(SUM(COUNTIFS($C$5:$C$85,"SECU0003",E5:E85,{"P","L","D","E","A"}))))*100

I was really puzzled as to why I was getting an error message when I dragged the formula right - but it turned out to be due to trailing spaces in the data of the adjacent column. I used the TRIM function to get rid of them and then the formula worked fine.

Thanks so much for your help!!!

 

[Fluff]

Glad you sorted it & thanks for the feedback