# formula that counts

1. ## formula that counts

hello all
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

2. Try:

=SUMPRODUCT((Sheet1!\$M\$2:\$M\$25=TRIM(\$F\$16))*(Sheet1!\$G\$2:\$G\$25="y"))

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...

3. 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

4. Maybe you mean?

``Please Login or Register  to view this content.``

5. 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...

6. Originally Posted by DonkeyOte
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...
No problem...

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... ;(

7. superb as ever donkeyote and nbvc thank you

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1