formula that counts
im having trouble trying to get a formula that can count
in sheet 2 cell g16 i need it to count how many times "horse racing" appears in sheet 1 column m and also for it to count how many "y" are in column g of sheet 1 and return something like
4 from 4
this example is taken from the attached sheet
basically it needs to count the yesses in column g that correspond to the horse racing in column n
so there could be 7 instances of horse racing ( 4 in this example ) and count the y's in column g which could be 2 ( 4 in this example )
thanks to anyone who can help
would like to say thanks to nbvc for his previous help
Last edited by excellentexcel; 01-13-2009 at 09:56 AM.
BTW: You must be careful about extra spaces... the text in F16 has an extra space at the end.. hence the use of my TRIM() function..but it shouldn't be required because your text should be consistently void of extra spacing... It will definitely cause you less heartache when creating formulas...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
hi nbvc thanks for your help it really is appreciated
it doesnt produce the correct output
yesses from qty
ie column m ( lets use horse racing )
4 horse racing in column n
and there are 4 "y" in column g that correspond to horse racing in column n
therefore out put into sheet 2 cell g16 should be
"4 from 4" without the quotes
EDIT: ...or perhaps the other way around
=SUMPRODUCT(--(Sheet1!$M$2:$M$25=TRIM($F16)),--(Sheet1!$G$2:$G$25="Y"))&" from "&COUNTIF(Sheet1!$M$2:$M$25,TRIM($F16))
(wins from count)
Apologies NBVC -- your post wasn't here when I composed...
Originally Posted by DonkeyOte
Actually, I think I have them reversed and yours is probably the way the OP wants it... 4 from 4 wasn't a great example for testing... ;(
superb as ever donkeyote and nbvc thank you
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1