Emma has attended:
Excel Introduction course
Formulas
i have to make a sheet with customers name and if they are a payer or member. I know the =countif(cells,"P") for a payer and m member. How if possible can i do it with a name infront of the P or M, so it will give me the amount of each. I need the clients name.
Many Thanks Emma
RE: formulas
Hi Emma
Thanks for getting in touch. This should be possible. Can you reply with a few examples of the sort of things your sheet says? How does the name and the "P" or "M" fit together?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: formulas
OK.... I have a daily sheet with classes (I work in a gym). We have a break down of members and non members, who are classed as payers.
this is an example...
Jo M ( this is a member)
Fred P ( this is a payer)
June M
Anne M
John P
Louise P
Chris P
Jack M
each payer pays £6.00 I know how to do the =sum(cells*6), to give me the full amount, but I need a formula to give me the amount of payers to start with. And the amount of members...
so I have something like this at the end of each column,
payers 10
£60.00
members 5
Many thanks Emma
RE: formulas
Hi Emma
Thanks for this, it's really helpful. Just one last detail to sort this out: when you have "Fred P" is all of that in one cell? Or is "Fred" in one cell and "P" in the next?
Then I should be able to let you know what's formulas you need.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: formulas
yes its all in one cell.
I did think about having the name in one cell and P in another, but the staff have to type really quickly, ask names and take money, and I know they will make mistakes. They do even now just spelling names !!!
RE: formulas
Hi Emma
OK, I would use a RIGHT function to pull out the "M" or the "P". It looks like this:
=RIGHT(A1,1)
which pulls out the rightmost character from that cell. You can then use a COUNTIF function as above to figure out which cells have an M or P beside them.
I've enclosed an example to show you how it works.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
GF Members Payers.xlsx
