excel Help

[olibooth]

hi all.

ive been trying all day and struggling. I have a spreadsheet for work audits. I have them set up in month order and the cell gets either a P,A or F in it.
ive been to count how many P,A and F there are in each month and add up as it goes.

ive managed to turn the letter it in a number with this formula:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(ISNUMBER(SEARCH("p",G3)),1,"")
but that's only work on one cell, I need it to do this over 6 cells.[/FONT]


Any help would be great.

thanks

 

[jtakw]

Hi,

Not entirely sure what End result you're looking for, a Total of P, A, or F individually or combined?

But look into the COUNTIF or COUNTIFS function.

 

[olibooth]

thanks for the quick reply

i'll set the scene to try and help.
each engineer gets upto 6 audits a month. each one of them can be a Pass, Advisory or Fail. which i enter in manually.
i have a summary page that i need to know how many Pass, Advisory or Fails the engineer has had for the year.

hope that makes sense

 

[jtakw]

Again, look into the COUNTIF or COUNTIFS function, but depending on how your data is setup, which you haven't given any sample, you might need SUMPRODUCT.

This is a sample using COUNTIF:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Engineer	Jan	Feb	Mar	Apr	May	Jun		Pass	Advisory	Fail
2	John Doe	Pass	Advisory	Pass	Fail	Advisory	Fail		2	2	2
3	Jane Doe	Advisory	Pass	Pass	Pass	Advisory	Pass		4	2	0
Sheet526

Cell Formulas
Range	Formula
I2	=COUNTIF($B2:$G2,I$1)

I2 formula copied down and across to K2

 

[olibooth]

thank you so much, this has worked a treat.

think i was over complicating it to much by trying to turn the letter into numbers first.

 

[jtakw]

You're welcome, welcome to the forum.

Just post back if you need further assistance.