There is nothing worse than having to manually count anything on a spreadsheet...well, there are bigger life problems...but let's solve one thing at a time.
Yes, you can make a new row at the top and then add in totals but that is not the best looking. Let's combine text with a formula to create our counter.
Here is our starting data: (Sample Spreadsheet - just click "Use Template")
Let's count the number of songs:
In A1:
- start with ="Song (" &
- concatenates (fancy word for combine/add) the string "Song (" with something
- ="Song (" & countA(A2:A)
- Count the number of non-blank cells (including text and numbers) from cell A2 all the way down
- ="Song (" & countA(A2:A) & ")"
- Add in the last bracket
Results:
This works really well when dealing with Form inputs where the data keeps increasing.
Let's get a little filter look up working:
Now, Data Validation has a neat little feature where it will only show unique values. We'll use this to our advantage.
In E1: Enter in "Lookup"
In E2 and F2: Enter in "Song" and "Artist(s)" respectively.
Click on F1:
- Select "Data -> Data validation" then click on "+ Add rule"
- Under "Criteria" select "Dropdown (from a range)"
- In the box below, type in "Sheet1!C2:C" and press <enter> or select the grid, click on the new window's box and then highlight the data.
- Make sure that the range is from C2 (i.e. just below the header) to C (so any additional data will be added)
I'm not a fan of the "chip" look so scroll down and click on "Advanced options", under "Display style" choose "Arrow".
Click on the F1 cell to activate the drop down and see if there are any repeats. Thanks to the quirk in Data Validation, there should be none.
Let's add the filter in to complete this task.
In E3:
- =filter(A:B
- This will only display the song and the artist(s) based on the genre we select
- =filter(A:B,C:C=F1)
- This checks the C column (genre) and make sure it matches what is picked in F1 (our new data validation drop down)
And nothing happens, not everything is exciting.
Click on F1 and pick a genre...now it gets interesting.
Think you got it? Great, now make a count for the filtered songs in cell E2.
Now, there are a couple of problems since we used a basic function like filter. Anyone find them? Post them in the comments below.
I'll swing back with a more complex solution in another entry if I get enough "queries"...or maybe no answers at all?
Comments ()