200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
hi
i want a formula that i can assign the M and N letters to nimbers 7 and 12 that i write M in one cell and in otthers it gives me the number also it sums that amount of cells
i did the first part with if condition but i dont know how to make it get the letter M and uses the number 7 instead of it
i explain it more
if i put M in cell A1 to A10 give me Number 7 for each one and in cell A11 combine them all for me but minus 10
Hi
If you want 7 for each M and 10 for each N, just count the occurrences of each in the range and multiply by 7 as appropriate, so in cell A11 put this:
=COUNTIF(A1:A10,"M")*7 +COUNTIF(A1:A10,"N")*10or some other cell and change the range in bold to whatever you need.
If you also need to deduct one for each occurrrence in the range (you said), use COUNTA to count the non-blank cells so use:
=COUNTIF(A1:A10,"M")*7 +COUNTIF(A1:A10,"N")*10-COUNTA(A1:A10)