Sheets: Using Text from Another Cell as a Parameter for a Function

Try it this way in I2:

=COUNTIF($A$3:$D$26, H2)

then copy down. This works in Excel, so I would assume it should also work in Google Sheets as it is fairly basic.

Hope this helps.

Pete

Have a look at the link. A formula in K2 to extract all the unique names, and a formula in L2 counts the number of times the name appears. Hope this is what you're after.

https://docs.google.com/spreadsheets...it?usp=sharing

Originally Posted by Pete_UK

Try it this way in I2:

=COUNTIF($A$3:$D$26, H2)

then copy down. This works in Excel, so I would assume it should also work in Google Sheets as it is fairly basic.

Hope this helps.

Pete

I could have sworn I already tried this, at that it didn't work, but when I do it now, it works exactly as expected! I don't know what sorts of funky witchcraft you did, but I'll take it!

Originally Posted by Flyboy65

Have a look at the link. A formula in K2 to extract all the unique names, and a formula in L2 counts the number of times the name appears. Hope this is what you're after.

This wasn't really what I meant, but it works much better at accomplishing the original task! This is super neat, thanks so much for showing me this.

Okay, I got 2 really good solutions for my bafflingly-simple problem, but there's something else. In another project, I'm trying to reference data from a separate spreadsheet: =average(filter(Rocketbelt!F2:F, Rocketbelt!B2:B=B2))

Now, the contents of cell A2 happen to be the string "Rocketbelt", but there are spreadsheets with different names that I also would like to reference, and those names are just in the A column of the sheet. Is there any way that I can alter this formula to automatically point to the correct spreadsheet, or would I need to just manually adjust it each time I'm referring to a new sheet?

Do you have the INDIRECT function in Google sheets? You would use it like this:

INDIRECT("'"&A2&"'!F2:F100")

and:

INDIRECT("'"&A2&"'!B2:B100")

Hope this helps.

Pete

Originally Posted by Pete_UK

Do you have the INDIRECT function in Google sheets? You would use it like this:

INDIRECT("'"&A2&"'!F2:F100")

and:

INDIRECT("'"&A2&"'!B2:B100")

You are a genius and a scholar! By using INDIRECT I can concatenate the string values, and INDIRECT still returns the ranges that FILTER needs to do its thing!

It took me some fiddling, but what had previously been hard-coded as:

=average(filter(Rocketbelt!$H$2:$H, Rocketbelt!$B$2:$B=B2))

with me having to change the string every 6 lines or so can now be written as:

=average(filter(INDIRECT("'"&A2&"'!$H$2:$H"), INDIRECT("'"&A2&"'!$B$2:$B")=B2))

and when I drag it across the entire column, everything works out exactly as it should! I can't thank you enough, but I'll say it anyway: thanks!

Glad to help, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Originally Posted by Pete_UK

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Done and double-done, for everyone's who's posted replies here.