#countunique

[bizinfograph1]

Countunique Function In Excel

Learn how to use the countunique function in excel to efficiently count the number of unique values within a specified range, helping you analyse data with accuracy and precision. To know more, watch this video and visit: https://www.bizinfograph.com/resource/how-to-count-unique-values-and-distinct-values-in-excel/

[minnichan]

Because I spreadsheet the fics I read I can actually do a wrap up of my reading activity.

From January to November 2023 I read 261 fics by 81 authors in 11 different fandoms* for a total of 2450311 words.**

* Just 6 fandoms if I group all the weilan derivatives shows as one fandom, which they are too me, but for convenience I only used the countunique function.

** not including rereads and fics I didn't like

September and October were busy months irl (only 3000 and 6000 words respectively) and I read more books instead. March is when I went through most of the fics by a single author.

ao3 wrapped

-> you read 2,690,420 words, none of which are in the bible

-> your preferred genre is an abomination

-> you found one writer and read everything they've ever written...

-> ...only to find out they're into some weird shit.

-> and now you are too. congratulations!

-> the data we've collected has shocked us to our very core and we wish we hadn't done it. we won't see you next year. please seek professional mental support

[you2018]

also i shouldnt be playing around in them when im tired it took me too long to realize that =minus(count(dates), countunique(dates)) wouldnt work bc i frequently watch more than 2 movies a day

[itzmedineshofficial]

6 ways to use COUNT function in Google Sheets with Multiple Criteria | E...

[teitpp]

In google sheets you can also program it to give you badges, if anyone will miss that feature of nano in particular.

You need 3 things.

1. A Badge List

This will store your badge images teeny tiny, your badge earned condition, and anything else you want (motivational message, badge name, whatever) add a row for each badge. you can create as many as you can think of ways to verify that you met the condition. google sheets and excel have really cool things you can do once you know a few key formulas. create a column that is just for storing the images and a separate column for the goal, badge name, whatever info you want to store.

2. Badge Display Board.

create a bunch of merged cells the size you want your badge to appear. until you have a bunch of nicely sized blank squares like picture frames (give these whatever border color you want too. colors are fun.) at least one for each badge you could possibly earn for yourself. under each frame if you want, add the name of the badge or put in a formula to record the badge description stored on your badge list.

3. A Writing Recording Sheet.

This is where you will make an entry each day or each time you do writing. you can enter data directly in the google sheet or link up with a google form if youre fancy (or if you dont like looking at long lists of data.)

Pulling it all together

once you have build all 3 of these things it is time to program those picture frames in your Badge Display Board to show the badges when you earn them. Inside each merged cell / badge picture frame, put an IF formula: =IF(conditional statement,result if true,result if false)

The result if true will be the cell in the Image List storing the specific image you want to earn. (So if your badge is stored in B6, enter ImageListSheetName!B6)

The result if false should be "" (a blank cell)

The conditional statement is the key to making this work. This will use a formula to look at the range of cells in your Writing Recording Sheet that you will be checking for the badge goal and it will check if what youve done so far meets the conditions for each achievement.

Here are a few ideas for those conditional statements, based on hypothetical badges. I use the range A:A as an example but you'll use the range of whatever column your dates/words/times/etc. are in. (Note: to check a range from a different sheet, write the shert name and an ! before the cell range. It will look like SheetName!A:A)

You want to earn a badge for making a certain number of entries on the Writing Recording Sheet (lets say 50 entries)

COUNTA(A:A)>49

You want to earn a badge when your total writing so far meets or exceeds a word count/total time goal (lets say 20,000)

SUM(A:A)>19,999

You want to earn a badge when you have a single writing day or session that meets or exceeds a certain amount (1000 words in a single day, say)

MAX(A:A)>999

You want to earn a badge for writing for say, 20 or more days overall (this works even if you make multiple entries a day)

COUNTUNIQUE(A:A)>19

You want to earn a badge for a writing streak (lets say a 7 day writing streak).

(This is the most complicated thing to tally in a single cell and i didn't come up with it. i found it on stack overflow because i'm not that level of wizard on my own.) (Note: this works if you record 1 entry per day.)

INDEX(COLUMNS(SPLIT(FLATTEN(SPLIT(TRIM(QUERY(IF(A2:A-A3:A=-1, 1, 0),,9^9)), " 0 ", )), " "))+1)>6

To make this conditional work when you make multiple writing entries a day...

1. Create a 4th sheet for Unique Dates

2. in whatever cell you choose (A1, Z27, Im not your computer science teacher do what you like, just make sure the rest of that column is completely blank) enter =UNIQUE(A:A). Replace A:A with the date range column in your Writing Recording Sheet) this formula will populate all the cells under that cell with all the unique dates in your Writing Recording Sheet in the order that they appear (so don't make out of order entries or it will get a bit screwy)

3. Now go back to the crazy complex conditional for our Streak Badge and replace A2:A-A3:A with your new date range on the Unique Dates sheet.

So it looks like NaNoWriMo are happy to have AI as part of their community. Miss me with that bullshit. Generative artificial intelligence is an active threat to creativity and the livelihoods of hundreds of thousands of people in creative fields.

Please signal boost this so writers can make an informed choice about whether to continue to take part in such a community.

[itunesbooks]

Google Sheet Functions - Barrie Roberts

Google Sheet Functions A step-by-step guide Barrie Roberts Genre: Software Price: $2.99 Publish Date: December 6, 2016 Publisher: Barrie Roberts Seller: Barrie Roberts The idea of this book is to show you how to use some of the most useful functions in Google Sheets. It starts with the basic ones like SUM and takes you through to more advanced areas like VLOOKUP, IMPORTRANGE, and QUERY. I feel the best way to learn these is by following examples and in every chapter I will take you through several examples showing you different aspects of the function and various uses of it. The examples start with the basic syntax of the function and build up to more complex formulas, but I explain each step along the way. So, don’t worry, these are much easier than maybe you think! CONTENT: Introduction to functions Chapter 1 - AVERAGE, MAX, MIN Chapter 2 - COUNT and COUNTA Chapter 3 - IF Chapter 4 - CONCATENATE Chapter 5 - VLOOKUP (inc IFERROR and ARRAYFORMULA) Chapter 6 - OR & AND Chapter 7 - COUNTIF, SUMIF, COUNTIFS, SUMIFS Chapter 8 - FILTER Chapter 9 - IMPORTRANGE Chapter 10 - PROPER, UPPER, LOWER, TRIM Chapter 11 - TRANSPOSE Chapter 12 - ISEMAIL, ISNUMBER, ISURL, NOT Chapter 13 - UNIQUE, COUNTUNIQUE, SORT Chapter 14 - NOW, TODAY, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND Chapter 15 - WEEKDAY, WORKDAY, NETWORKDAYS, EDATE, EOMONTH, CHOOSE Chapter 16 - GOOGLETRANSLATE, DETECTLANGUAGE Chapter 17 - OFFSET Chapter 18 - IMAGE Chapter 19 - ROUND, ROUNDUP, ROUNDDOWN Chapter 20 - HYPERLINK Chapter 21 - INDEX and MATCH Chapter 22 - QUERY http://bit.ly/2VCcQ8Z

[poundfooolish]

Also why doesn’t Excel have a fucking Countunique function like google docs why do I have to make ‘pivot tables’ or ‘filters’ like this is absurd and unhelpful I just want to know how many individual SRs I’m dealing with

[archiebwoollard]

Time Saving Automation: Search Query Edition

Search Query Reports (SQRs) – you either love them or hate them. While you probably pull these semi-regularly (or maybe you don’t do them at all), are you getting the most out of them? With a little bit of work on the front end, you can save time by automating these reports. Once you get it all set up, simply check in on the Google Sheets workbook every month (or whatever schedule you like to run your SQRs on).

  We’ve already covered SQRs here and here, but today we’ll cover alternative methods to automate your search query reports.

  Automation Set Up

  The Script

  Setting up this script allows us to segment search queries into “N-Grams”, which is a fancy way of saying “common phrases.” Once you get it up and running with a few tweaks to the code, you’ll get two sheets for each N-Gram, an account-level report and a campaign level report.

    N-Gram refers to the number of words in the phrase. You’ll also get a word count analysis sheet which shows you performance by query length.

  Supermetrics

  From Supermetrics, we want to pull in an aggregate view of the queries, along with a more detailed report that also pulls in campaign and ad group.

  *Note: If you don’t have a paid version of Supermetrics, you can either paste the data in manually or use the free version to pull SQR data from Google Analytics.

    Setting Up The Report

  From the Supermetrics report, we’ll use query functions to pull the following:

  Non-converting queries

High CPA queries

Cross pollution

  For each of the above reports, we pull an overview using an aggregate view of the queries, along with a more detailed report that pulls in keyword, campaign, and ad group data.

  Using query functions (appropriately named in this situation!), this is pretty easy. Just one formula to create each table.

    In this instance, column E is the conversions column.

    These queries can be easily modified to fit your needs. You also have the option to add additional sheets to the report to look at whatever else piques your interest (example: converting queries where the query =/= the keyword exactly).

  Cross Pollution

  For cross pollution, we’ll start with a simple query to count up the number of keywords, and sum the associated clicks, cost, conversions, etc. Then we’ll use an IF statement to determine whether or not it qualifies for cross pollution.

    Now, we’ll use the =countunique function along with the FILTER function to count the number of unique campaigns matching with each query.

    Next, we go back to the Query_Data sheet that contains our Supermetrics pulled data and add a few helper columns.

    A quick VLOOKUP allows us to pull in whether there is cross pollution for that term and the count of campaigns.

  From here, we use another query formula to filter the Query_Data sheet for terms that are cross polluting in the account, and are matching to more than 1 unique campaign. By ordering by the search term, it lines things up nicely.

    A Note on Cross Pollution

In some accounts, it would seem that there is a lot of cross pollution happening due to the campaign structure. For example, if you have multiple campaigns that are identical apart from their geo-targeting, this report will say that you have an alarming amount of cross pollution.

  Finding Takeaways

  So now you’ve got the bones of the report set up, how do we now find learnings and/or action items?

  For The Supermetrics Reports

  From the high CPA and non-converting terms reports, this is pretty simple. Just look for any queries that are underperforming or anything that’s really irrelevant. When you do find terms to pause or add to the account, use the campaign level report to see exactly where to pause it.

  From the cross pollution reports, look at which campaigns and keywords are capturing the same queries. How does performance differ? Use this information to better funnel your traffic, whether it’s with negative keywords, match type adjustments, etc.

  For The Script Reports

  For each of the N-Gram reports, look for any common themes and high performing phrases. For the top performing phrases, consider taking a look at your SQR report and filtering for anything that contains the phrase to get a better idea of what kinds of queries these phrases are occurring in.

  The word count report can offer some interesting insight into CTR and conversion rates for longer tail queries vs. one or two-word searches. Keep this in mind when adding new keywords to the account, and think about the conversion funnel.

  Closing Thoughts

  While SQRs (probably) won’t be the thing that makes or breaks your account, it is a simple task that can have an impact on your account’s efficiency and performance. Many will skip this report, or do it very rarely, because of the time it takes to download the data and sift through it via zillions (okay, maybe an exaggeration…) of pivot tables.

  Taking some time to set up an automated report cuts down on a lot of friction and makes it a lot easier to pull out insights. As a bonus, if you’re managing multiple accounts, use this same template for both. Simply copy the Google Sheets file and switch out the accounts. Easy! Now go forth and mine those queries!

from RSSMix.com Mix ID 8217493 http://www.ppchero.com/time-saving-automation-search-query-edition/

[zacdhaenkeau]

Time Saving Automation: Search Query Edition

Search Query Reports (SQRs) – you either love them or hate them. While you probably pull these semi-regularly (or maybe you don’t do them at all), are you getting the most out of them? With a little bit of work on the front end, you can save time by automating these reports. Once you get it all set up, simply check in on the Google Sheets workbook every month (or whatever schedule you like to run your SQRs on).

  We’ve already covered SQRs here and here, but today we’ll cover alternative methods to automate your search query reports.

  Automation Set Up

  The Script

  Setting up this script allows us to segment search queries into “N-Grams”, which is a fancy way of saying “common phrases.” Once you get it up and running with a few tweaks to the code, you’ll get two sheets for each N-Gram, an account-level report and a campaign level report.

    N-Gram refers to the number of words in the phrase. You’ll also get a word count analysis sheet which shows you performance by query length.

  Supermetrics

  From Supermetrics, we want to pull in an aggregate view of the queries, along with a more detailed report that also pulls in campaign and ad group.

  *Note: If you don’t have a paid version of Supermetrics, you can either paste the data in manually or use the free version to pull SQR data from Google Analytics.

    Setting Up The Report

  From the Supermetrics report, we’ll use query functions to pull the following:

  Non-converting queries

High CPA queries

Cross pollution

  For each of the above reports, we pull an overview using an aggregate view of the queries, along with a more detailed report that pulls in keyword, campaign, and ad group data.

  Using query functions (appropriately named in this situation!), this is pretty easy. Just one formula to create each table.

    In this instance, column E is the conversions column.

    These queries can be easily modified to fit your needs. You also have the option to add additional sheets to the report to look at whatever else piques your interest (example: converting queries where the query =/= the keyword exactly).

  Cross Pollution

  For cross pollution, we’ll start with a simple query to count up the number of keywords, and sum the associated clicks, cost, conversions, etc. Then we’ll use an IF statement to determine whether or not it qualifies for cross pollution.

    Now, we’ll use the =countunique function along with the FILTER function to count the number of unique campaigns matching with each query.

    Next, we go back to the Query_Data sheet that contains our Supermetrics pulled data and add a few helper columns.

    A quick VLOOKUP allows us to pull in whether there is cross pollution for that term and the count of campaigns.

  From here, we use another query formula to filter the Query_Data sheet for terms that are cross polluting in the account, and are matching to more than 1 unique campaign. By ordering by the search term, it lines things up nicely.

    A Note on Cross Pollution

In some accounts, it would seem that there is a lot of cross pollution happening due to the campaign structure. For example, if you have multiple campaigns that are identical apart from their geo-targeting, this report will say that you have an alarming amount of cross pollution.

  Finding Takeaways

  So now you’ve got the bones of the report set up, how do we now find learnings and/or action items?

  For The Supermetrics Reports

  From the high CPA and non-converting terms reports, this is pretty simple. Just look for any queries that are underperforming or anything that’s really irrelevant. When you do find terms to pause or add to the account, use the campaign level report to see exactly where to pause it.

  From the cross pollution reports, look at which campaigns and keywords are capturing the same queries. How does performance differ? Use this information to better funnel your traffic, whether it’s with negative keywords, match type adjustments, etc.

  For The Script Reports

  For each of the N-Gram reports, look for any common themes and high performing phrases. For the top performing phrases, consider taking a look at your SQR report and filtering for anything that contains the phrase to get a better idea of what kinds of queries these phrases are occurring in.

  The word count report can offer some interesting insight into CTR and conversion rates for longer tail queries vs. one or two-word searches. Keep this in mind when adding new keywords to the account, and think about the conversion funnel.

  Closing Thoughts

  While SQRs (probably) won’t be the thing that makes or breaks your account, it is a simple task that can have an impact on your account’s efficiency and performance. Many will skip this report, or do it very rarely, because of the time it takes to download the data and sift through it via zillions (okay, maybe an exaggeration…) of pivot tables.

  Taking some time to set up an automated report cuts down on a lot of friction and makes it a lot easier to pull out insights. As a bonus, if you’re managing multiple accounts, use this same template for both. Simply copy the Google Sheets file and switch out the accounts. Easy! Now go forth and mine those queries!

from RSSMix.com Mix ID 8217493 http://www.ppchero.com/time-saving-automation-search-query-edition/

[maxslogic25]

Time Saving Automation: Search Query Edition

Search Query Reports (SQRs) – you either love them or hate them. While you probably pull these semi-regularly (or maybe you don’t do them at all), are you getting the most out of them? With a little bit of work on the front end, you can save time by automating these reports. Once you get it all set up, simply check in on the Google Sheets workbook every month (or whatever schedule you like to run your SQRs on).

  We’ve already covered SQRs here and here, but today we’ll cover alternative methods to automate your search query reports.

  Automation Set Up

  The Script

  Setting up this script allows us to segment search queries into “N-Grams”, which is a fancy way of saying “common phrases.” Once you get it up and running with a few tweaks to the code, you’ll get two sheets for each N-Gram, an account-level report and a campaign level report.

    N-Gram refers to the number of words in the phrase. You’ll also get a word count analysis sheet which shows you performance by query length.

  Supermetrics

  From Supermetrics, we want to pull in an aggregate view of the queries, along with a more detailed report that also pulls in campaign and ad group.

  *Note: If you don’t have a paid version of Supermetrics, you can either paste the data in manually or use the free version to pull SQR data from Google Analytics.

    Setting Up The Report

  From the Supermetrics report, we’ll use query functions to pull the following:

  Non-converting queries

High CPA queries

Cross pollution

  For each of the above reports, we pull an overview using an aggregate view of the queries, along with a more detailed report that pulls in keyword, campaign, and ad group data.

  Using query functions (appropriately named in this situation!), this is pretty easy. Just one formula to create each table.

    In this instance, column E is the conversions column.

    These queries can be easily modified to fit your needs. You also have the option to add additional sheets to the report to look at whatever else piques your interest (example: converting queries where the query =/= the keyword exactly).

  Cross Pollution

  For cross pollution, we’ll start with a simple query to count up the number of keywords, and sum the associated clicks, cost, conversions, etc. Then we’ll use an IF statement to determine whether or not it qualifies for cross pollution.

    Now, we’ll use the =countunique function along with the FILTER function to count the number of unique campaigns matching with each query.

    Next, we go back to the Query_Data sheet that contains our Supermetrics pulled data and add a few helper columns.

    A quick VLOOKUP allows us to pull in whether there is cross pollution for that term and the count of campaigns.

  From here, we use another query formula to filter the Query_Data sheet for terms that are cross polluting in the account, and are matching to more than 1 unique campaign. By ordering by the search term, it lines things up nicely.

    A Note on Cross Pollution

In some accounts, it would seem that there is a lot of cross pollution happening due to the campaign structure. For example, if you have multiple campaigns that are identical apart from their geo-targeting, this report will say that you have an alarming amount of cross pollution.

  Finding Takeaways

  So now you’ve got the bones of the report set up, how do we now find learnings and/or action items?

  For The Supermetrics Reports

  From the high CPA and non-converting terms reports, this is pretty simple. Just look for any queries that are underperforming or anything that’s really irrelevant. When you do find terms to pause or add to the account, use the campaign level report to see exactly where to pause it.

  From the cross pollution reports, look at which campaigns and keywords are capturing the same queries. How does performance differ? Use this information to better funnel your traffic, whether it’s with negative keywords, match type adjustments, etc.

  For The Script Reports

  For each of the N-Gram reports, look for any common themes and high performing phrases. For the top performing phrases, consider taking a look at your SQR report and filtering for anything that contains the phrase to get a better idea of what kinds of queries these phrases are occurring in.

  The word count report can offer some interesting insight into CTR and conversion rates for longer tail queries vs. one or two-word searches. Keep this in mind when adding new keywords to the account, and think about the conversion funnel.

  Closing Thoughts

  While SQRs (probably) won’t be the thing that makes or breaks your account, it is a simple task that can have an impact on your account’s efficiency and performance. Many will skip this report, or do it very rarely, because of the time it takes to download the data and sift through it via zillions (okay, maybe an exaggeration…) of pivot tables.

  Taking some time to set up an automated report cuts down on a lot of friction and makes it a lot easier to pull out insights. As a bonus, if you’re managing multiple accounts, use this same template for both. Simply copy the Google Sheets file and switch out the accounts. Easy! Now go forth and mine those queries!

from RSSMix.com Mix ID 8217493 http://www.ppchero.com/time-saving-automation-search-query-edition/

[racheltgibsau]

Time Saving Automation: Search Query Edition

Search Query Reports (SQRs) – you either love them or hate them. While you probably pull these semi-regularly (or maybe you don’t do them at all), are you getting the most out of them? With a little bit of work on the front end, you can save time by automating these reports. Once you get it all set up, simply check in on the Google Sheets workbook every month (or whatever schedule you like to run your SQRs on).

  We’ve already covered SQRs here and here, but today we’ll cover alternative methods to automate your search query reports.

  Automation Set Up

  The Script

  Setting up this script allows us to segment search queries into “N-Grams”, which is a fancy way of saying “common phrases.” Once you get it up and running with a few tweaks to the code, you’ll get two sheets for each N-Gram, an account-level report and a campaign level report.

    N-Gram refers to the number of words in the phrase. You’ll also get a word count analysis sheet which shows you performance by query length.

  Supermetrics

  From Supermetrics, we want to pull in an aggregate view of the queries, along with a more detailed report that also pulls in campaign and ad group.

  *Note: If you don’t have a paid version of Supermetrics, you can either paste the data in manually or use the free version to pull SQR data from Google Analytics.

    Setting Up The Report

  From the Supermetrics report, we’ll use query functions to pull the following:

  Non-converting queries

High CPA queries

Cross pollution

  For each of the above reports, we pull an overview using an aggregate view of the queries, along with a more detailed report that pulls in keyword, campaign, and ad group data.

  Using query functions (appropriately named in this situation!), this is pretty easy. Just one formula to create each table.

    In this instance, column E is the conversions column.

    These queries can be easily modified to fit your needs. You also have the option to add additional sheets to the report to look at whatever else piques your interest (example: converting queries where the query =/= the keyword exactly).

  Cross Pollution

  For cross pollution, we’ll start with a simple query to count up the number of keywords, and sum the associated clicks, cost, conversions, etc. Then we’ll use an IF statement to determine whether or not it qualifies for cross pollution.

    Now, we’ll use the =countunique function along with the FILTER function to count the number of unique campaigns matching with each query.

    Next, we go back to the Query_Data sheet that contains our Supermetrics pulled data and add a few helper columns.

    A quick VLOOKUP allows us to pull in whether there is cross pollution for that term and the count of campaigns.

  From here, we use another query formula to filter the Query_Data sheet for terms that are cross polluting in the account, and are matching to more than 1 unique campaign. By ordering by the search term, it lines things up nicely.

    A Note on Cross Pollution

In some accounts, it would seem that there is a lot of cross pollution happening due to the campaign structure. For example, if you have multiple campaigns that are identical apart from their geo-targeting, this report will say that you have an alarming amount of cross pollution.

  Finding Takeaways

  So now you’ve got the bones of the report set up, how do we now find learnings and/or action items?

  For The Supermetrics Reports

  From the high CPA and non-converting terms reports, this is pretty simple. Just look for any queries that are underperforming or anything that’s really irrelevant. When you do find terms to pause or add to the account, use the campaign level report to see exactly where to pause it.

  From the cross pollution reports, look at which campaigns and keywords are capturing the same queries. How does performance differ? Use this information to better funnel your traffic, whether it’s with negative keywords, match type adjustments, etc.

  For The Script Reports

  For each of the N-Gram reports, look for any common themes and high performing phrases. For the top performing phrases, consider taking a look at your SQR report and filtering for anything that contains the phrase to get a better idea of what kinds of queries these phrases are occurring in.

  The word count report can offer some interesting insight into CTR and conversion rates for longer tail queries vs. one or two-word searches. Keep this in mind when adding new keywords to the account, and think about the conversion funnel.

  Closing Thoughts

  While SQRs (probably) won’t be the thing that makes or breaks your account, it is a simple task that can have an impact on your account’s efficiency and performance. Many will skip this report, or do it very rarely, because of the time it takes to download the data and sift through it via zillions (okay, maybe an exaggeration…) of pivot tables.

  Taking some time to set up an automated report cuts down on a lot of friction and makes it a lot easier to pull out insights. As a bonus, if you’re managing multiple accounts, use this same template for both. Simply copy the Google Sheets file and switch out the accounts. Easy! Now go forth and mine those queries!

from RSSMix.com Mix ID 8217493 http://www.ppchero.com/time-saving-automation-search-query-edition/

[bizinfograph1]

Countunique Excel Formula

Learn how to use the countunique function in Excel to efficiently count the number of unique values within a given range. Improve your data analysis skills with this powerful excel feature. To know more, watch this video and visit: https://www.bizinfograph.com/resource/how-to-count-unique-values-and-distinct-values-in-excel/