98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Formulas
Formulas
Resolved · Medium Priority · Version 2007
Wed 30 Oct 2013, 09:29Delegate Emma said...
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
Thu 31 Oct 2013, 10:00Trainer Gary said...
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
Thu 31 Oct 2013, 13:29Delegate Emma said...
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
Thu 31 Oct 2013, 16:08Trainer Gary said...
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
Thu 31 Oct 2013, 16:12Delegate Emma said...
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 !!!
Thu 31 Oct 2013, 16:32
Edited on Thu 31 Oct 2013, 16:33Trainer Gary said...
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
Attached files...
GF Members Payers.xlsx
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Autofit column width – Excel (all versions)a. Highlight the column or columns you wish to alter the width of. You do this by clicking on the grey button at the top of the column showing the column letter. Click and drag on these letters to select more than one column. |
