{"id":12042,"date":"2019-07-18T18:03:10","date_gmt":"2019-07-18T15:03:10","guid":{"rendered":"https:\/\/railsware.com\/blog\/?p=12042"},"modified":"2024-07-09T14:59:38","modified_gmt":"2024-07-09T11:59:38","slug":"data-validation-income-monitor","status":"publish","type":"post","link":"https:\/\/railsware.com\/blog\/data-validation-income-monitor\/","title":{"rendered":"How to Track Income in Google Sheets: Data Validation + Filter Formula"},"content":{"rendered":"\n<p class=\"intro-text\">Last time we talked about different options for <a href=\"https:\/\/railsware.com\/blog\/gsheets-data-validation\/\" target=\"_blank\" rel=\"noopener noreferrer\">validating data in Google Sheets<\/a>. You\u2019ve already learned how to create simple drop-down lists using Data Validation. Today, we\u2019re going to dive deeper. You\u2019ll discover how to build business metrics monitors using Data Validation, the FILTER formula, and dependent drop-down lists. Also, you\u2019ll learn a few formatting tips and tricks.<\/p>\n\n\n\n<p>If you prefer watching to reading, check out the YouTube version of this tutorial.<\/p>\n\n\n\n<div class=\"aspect-ratio\"><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/JZQSu6z1t-M\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Why you need data filtering<\/h2>\n\n\n\n<p>When you need to filter data in Google Sheets, it means you have to set rules to display certain information. This function is rather useful when you work with large datasets. Filtering lets you focus on relevant data and increase your performance.<\/p>\n\n\n\n<p>That\u2019s it for the boring theory stuff. Let\u2019s try hands-on. For this, you can get a personal copy of the <a href=\"http:\/\/bit.ly\/2QuUFAL\" target=\"_blank\" rel=\"noopener noreferrer\">Google Sheets practice file<\/a> with Data Validation task for exercising. As a dataset, we\u2019ll use the payments database we often use in other Google Sheets videos.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"500\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1.jpg\" alt=\"Data Validation in Google Sheets \" class=\"wp-image-12055\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1-360x150.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1-768x320.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1-1024x427.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\">How to create an income monitor<\/h2>\n\n\n\n<p>Imagine you want to have an income monitor based on the locations. There are clients from different regions, countries, and cities. You need the results to change depending on the exact location you select.<\/p>\n\n\n\n<p>Moreover, you can connect the income monitor to your CRM app and get it updated automatically using Coupler.io. Check out the blog post, <a href=\"https:\/\/blog.coupler.io\/google-sheets-sales-dashboard\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Build Sales Tracker with Google Sheets<\/a>, to learn details.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Dynamic drop-down lists<\/h3>\n\n\n\n<p>First, create a dynamic drop-down list. For this, get a separate tab called \u201cIncome Monitor\u201d. We recommend you store data sources and calculations on separate tabs. Now, mark the monitor and freeze the first row, as it will contain headers. You can do this by pulling the marker.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"860\" height=\"410\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content2.jpg\" alt=\"Google Sheets data validation\" class=\"wp-image-12056\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content2.jpg 860w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content2-360x172.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content2-768x366.jpg 768w\" sizes=\"auto, (max-width: 860px) 100vw, 860px\" \/><\/figure><\/div>\n\n\n<p>Then, mark where you\u2019ll have <strong>Region<\/strong>, <strong>Country<\/strong>, and <strong>City<\/strong> in the monitor. Now, let\u2019s apply the standard data validation to the region. To do this, click on the cell next to it, use Control+Option+D (Ctrl+Alt+D for Windows) and then press V. Or you may right-click on the cell and select Data Validation in the bottom of the list. In the menu, set the <strong>List from a range<\/strong> criteria and select the <strong>Regions<\/strong> column in the Payments Tab. Don\u2019t forget to specify the whole column in the <strong>Select a data range<\/strong> box, so that you don\u2019t miss anything when the new data is entered. For this, enter <em>&#8216;Payments&#8217;!C2:C<\/em>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"640\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content3.jpg\" alt=\"Gsheets data validation\" class=\"wp-image-12057\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content3.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content3-360x192.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content3-768x410.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content3-1024x546.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>Google Sheets automatically pulls <strong>unique values<\/strong> to the dropdown list, so that you don\u2019t see duplicates. Here is what you should get in your <strong>Income Monitor<\/strong> tab.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"350\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content4.jpg\" alt=\"Spreadsheet data validation\" class=\"wp-image-12058\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content4.jpg 630w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content4-360x200.jpg 360w\" sizes=\"auto, (max-width: 630px) 100vw, 630px\" \/><\/figure><\/div>\n\n\n<p>Also, let\u2019s pull unique regions to the list on the right, so it will be visible. It will be easier to validate the region based on the list in the same tab. So, let\u2019s use these values for data validation in the monitor.<\/p>\n\n\n\n<p>Select a cell and use braces to add a header right in the formula. Start from the header and then write down the formula:<br><code>={\"Regions\";<br>\nUnique(Payments!C2:C)}<\/code><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"350\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.1.jpg\" alt=\"Data Validation in Google Sheets \" class=\"wp-image-12059\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.1.jpg 630w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.1-360x200.jpg 360w\" sizes=\"auto, (max-width: 630px) 100vw, 630px\" \/><\/figure><\/div>\n\n\n<p>The unique formula pulls unique values from the <strong>Regions<\/strong> column in the Payments tab. You may split the formula into few lines to make it more readable. Press Enter.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"300\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.2.jpg\" alt=\"Google Sheets data validation\" class=\"wp-image-12060\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.2.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.2-360x90.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.2-768x192.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content5.2-1024x256.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\">Dependent drop-down lists<\/h3>\n\n\n\n<p>It will be easier to see the list of cities only from the selected regions and countries. Besides, you won\u2019t have to remind yourself whether Bergen is in Sweden or Norway every time. So let\u2019s use the dependent drop-down lists.<\/p>\n\n\n\n<p>For this, you need to create a few helpers. First, use the Filter formula to list the countries from the selected region. You can learn more about how to use Filter formulas in <a href=\"https:\/\/www.youtube.com\/watch?v=rIZ3CrjygRY&amp;t=1s\" target=\"_blank\" rel=\"noopener noreferrer\">our tutorial<\/a>. Select a cell and write down:<br><code>={\"Countries\";<br>\nFILTER(Payments!D2:D,Payments!C2:C=B2)<br>\n}<\/code><\/p>\n\n\n\n<p><code>\"Countries\"<\/code> refers to the header.<br><code>Payments!D2:D<\/code> denotes the range to filter (the countries column in the Payments tab).<br><code>Payments!C2:C=B2<\/code> denotes the criteria to match (the region column in Payments) to match the region selected in the monitor.<br><code>FILTER(Payments!D2:D,Payments!C2:C=B2)<\/code> refers to filtering the countries where the region is equal to the one selected in our monitor.<br>Make sure you\u2019ve formatted your formula and hit Enter.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"310\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content6.jpg\" alt=\"Gsheets data validation\" class=\"wp-image-12061\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content6.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content6-360x93.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content6-768x198.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content6-1024x265.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>Ok, so now you have the whole list of countries from the selected region filtered out here.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"500\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content7.jpg\" alt=\"Spreadsheet data validation\" class=\"wp-image-12062\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content7.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content7-360x150.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content7-768x320.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content7-1024x427.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>To get rid of the duplicates, you can apply UNIQUE. Wrap the FILTER in parents and put UNIQUE before it. This will pull the unique values from the filtered list.<\/p>\n\n\n\n<p><code>={\"Countries\";<br>\nUnique(<br>\nFILTER(Payments!D2:D,Payments!C2:C=B2)<br>\n)<br>\n}<\/code><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"360\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content8.jpg\" alt=\"Data Validation in Google Sheets \" class=\"wp-image-12063\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content8.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content8-360x108.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content8-768x230.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content8-1024x307.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>Now, if you select a different region, the countries list will change as well.<\/p>\n\n\n\n<p>Let\u2019s apply data validation to the <strong>Country<\/strong> in our monitor. <strong>Control+Option+D (Ctrl+Alt+D for Windows)<\/strong>, then V, or right-click on the cell and select Data Validation in the bottom of the list. As a data range, we select <strong>Countries<\/strong> (L2:L).<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"460\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9.jpg\" alt=\"Google Sheets data validation\" class=\"wp-image-12064\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9-360x138.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9-768x294.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9-1024x393.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>Now, if the region changes, the country might get marked as invalid.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"340\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9eg.jpg\" alt=\"Gsheets data validation\" class=\"wp-image-12065\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9eg.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9eg-360x102.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9eg-768x218.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content9eg-1024x290.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>Do the same to validate <strong>City<\/strong>. But here you need two conditions in the FILTER formula &#8211; for both Region and Country to match. So add one more condition.<\/p>\n\n\n\n<p><code>={\"Cities\";<br>\nUnique(<br>\nFILTER(Payments!E2:E,Payments!D2:D=B3,Payments!C2:C=B2)<br>\n)<br>\n}<\/code><\/p>\n\n\n\n<p><code>Payments!E2:E<\/code> &#8211; set to filter<br><code>Payments!D2:D=B3<\/code> &#8211; Countries to match the one in the monitor<br><code>Payments!C2:C=B2<\/code> &#8211; Region to match the one in the monitor<\/p>\n\n\n\n<p>Hit enter.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"330\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content10.jpg\" alt=\"Spreadsheet data validation\" class=\"wp-image-12066\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content10.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content10-360x99.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content10-768x211.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content10-1024x282.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>If for some reason, Region and Country don\u2019t match, the Cities list won\u2019t show. It may happen when you change Region but not Country. This is an additional reminder for you to select the matching region and country.<\/p>\n\n\n\n<p>Now, add the Cities range as a criteria to validate City in our monitor. Perform the regular operation to apply data validation and select a data range (N2:N). You can also mark Reject input on invalid data and add a note (mark <strong>Show validation help text<\/strong>) \u201cSelect a city from the list\u201d.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1080\" height=\"500\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content11.jpg\" alt=\"Data Validation in Google Sheets \" class=\"wp-image-12067\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content11.jpg 1080w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content11-360x167.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content11-768x356.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content11-1024x474.jpg 1024w\" sizes=\"auto, (max-width: 1080px) 100vw, 1080px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\">Color coding to format a spreadsheet<\/h2>\n\n\n\n<p>If you want to visually mark the structure, <strong>color coding<\/strong> can help you with this. It is also very helpful when multiple users work with your spreadsheet. You can color the cells with formulas, as well as the cells with dynamic data, to make sure nobody re-writes them.<\/p>\n\n\n\n<p>Use different colors for outputs and calculations. You can also delete the unneeded rows. To make datasets visually independent from each other, add color boundaries between them (like in columns K and M).<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"290\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content12.jpg\" alt=\"Google Sheets data validation\" class=\"wp-image-12068\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content12.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content12-360x87.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content12-768x186.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content12-1024x247.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\">Calculating income<\/h2>\n\n\n\n<p>Now, it\u2019s time to calculate values you want to see. As an example, let\u2019s find out the total income from the clients in a particular city. This will be Bergen.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>SUM(FILTER<\/strong><\/li>\n\n\n\n<li>Select the Payment Amount column from the datasource to be filtered &#8211; Payments!G2:G<\/li>\n\n\n\n<li>Select criteria to match the values above\n<ul class=\"wp-block-list\">\n<li>Region in the datasource to match the Region selected in the monitor &#8211; Payments!C2:C=B2<\/li>\n\n\n\n<li>Country in the datasource to match the Country selected in the monitor &#8211; Payments!D2:D=B3<\/li>\n\n\n\n<li>City in the datasource to match the City selected in the monitor &#8211; Payments!E2:E=B4<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p>You should get the following:<br><code>=SUM(<br>\n    Filter(Payments!G2:G,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)<br>\n)<\/code><\/p>\n\n\n\n<p>To trap and manage errors, apply IFERROR formula to the Filter. If an error occurs, add a message \u201cCheck if Region, Country, and City match\u201d. Here how it looks:<\/p>\n\n\n\n<p><code>=IFERROR(<br>\n  SUM(<br>\n    Filter(Payments!G2:G,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)<br>\n),<br>\n\"check if Region, Country, and City match\")<\/code><\/p>\n\n\n\n<p>And here it is &#8211; the sum of the payments based on the multiple criteria.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"360\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content13.jpg\" alt=\"Gsheets data validation\" class=\"wp-image-12069\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content13.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content13-360x108.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content13-768x230.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content13-1024x307.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure><\/div>\n\n\n<p>You can apply this approach to different contexts:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>to see the list of clients in the region<\/li>\n\n\n\n<li>to track <a href=\"https:\/\/www.billdu.com\/blog\/unpaid-invoice\/\" target=\"_blank\" rel=\"noopener\" title=\"unpaid invoices\">unpaid invoices<\/a>, etc. <p>\u00a0<\/p> <p>Using other formulas, like FILTER, provides more capabilities to track data. FILTER lets you pull information about clients and their payments based on the Region, Country, and City you select. For this:<\/p> <ul> <li>add headers first &#8211; open braces and pull all the headers from the datasource (from A1 to H1) &#8211; <code>=Payments!A1:H1<\/code>;<\/li> <li>type <code>IFERROR(FILTER(<\/code> and specify values from A2:H and the filter criteria (Region, Country, and City) to match the values from the monitor &#8211; <code>Payments!A2:H,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4<\/code><\/li> <\/ul> <p>Here how it looks:<br><code>={Payments!A1:H1;<br> IFERROR(<br> Filter(Payments!A2:H,Payments!C2:C=B2,Payments!D2:D=B3,Payments!E2:E=B4)<br> )<br> }<\/code><\/p> <p>If all criteria match, you\u2019ll see a list of payments from different clients.<\/p> <figure><img loading=\"lazy\" decoding=\"async\" width=\"1200\" height=\"420\" class=\"aligncenter size-full wp-image-12070\" src=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content14.jpg\" alt=\"Spreadsheet data validation\" srcset=\"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content14.jpg 1200w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content14-360x126.jpg 360w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content14-768x269.jpg 768w, https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content14-1024x358.jpg 1024w\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" \/><\/figure> <p>If criteria don\u2019t match, the list will be empty. You can discover more tips and <a href=\"https:\/\/www.youtube.com\/watch?v=aoRwX1cwWrY\" target=\"_blank\" rel=\"noopener noreferrer\">tricks with Filter formula<\/a> in our video.<\/p> <p>That\u2019s it, you income monitor is ready to use. Google Sheets still has many quirks, and the Railsware team will be demystifying them for its readers and viewers in upcoming posts. So, read our blog and subscribe to our <a href=\"https:\/\/www.youtube.com\/c\/railsware?utm_source=website&amp;utm_medium=referral&amp;utm_campaign=gs_article_dv_advanced\" target=\"_blank\" rel=\"noopener noreferrer\">YouTube channel<\/a> to get more interesting insights.<\/p> <\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>What if you could build an income monitor for your business? Check our hands-on guide on how to do this and validate data in Google Sheets using filter formula<\/p>\n","protected":false},"author":72,"featured_media":12071,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[365,597],"tags":[],"coauthors":["Anastasiia Honcharova"],"class_list":["post-12042","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-insights","category-railsware-academy"],"acf":[],"aioseo_notices":[],"categories_data":[{"name":"Insights","link":"https:\/\/railsware.com\/blog?category=insights"},{"name":"Railsware Academy","link":"https:\/\/railsware.com\/blog?category=railsware-academy"}],"post_thumbnails":"https:\/\/railsware.com\/blog\/wp-content\/uploads\/2019\/07\/DatavalidationinGoogleSheetsAdvanced-Content1.jpg","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12042","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/users\/72"}],"replies":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/comments?post=12042"}],"version-history":[{"count":8,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12042\/revisions"}],"predecessor-version":[{"id":17429,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/posts\/12042\/revisions\/17429"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media\/12071"}],"wp:attachment":[{"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/media?parent=12042"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/categories?post=12042"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/tags?post=12042"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/railsware.com\/blog\/wp-json\/wp\/v2\/coauthors?post=12042"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}