How to count several nonblank rows based on specific criteria across two sheets
Hi Community!
I have two different grid sheets within smarthseet with the following information:
 In sheet 1 I have several products that need a certain number of photography shots and being delivered in different channels. Each row has a due date.
 In sheet 2 I want to find a formula that would give me the numbers in blue. So is there a formula that says, go to the shotlist sheet, and calculate how many handbags shots that are being delivered in a print chanel and due on the 17th (here there are 5 nonblank cells for example). And so on and so forth for each product and due date.
Thanks in advance for your help!
Answers

Hi @emka,
I don't recommend using your second sheet to solve this problem, because the formulas will be very complicated and hard to manage.
Instead, I recommend adjusting your first sheet a little bit, and then using a Report to track the number of shots for each date. Here's how I would do it.
In your first sheet, create 3 new columns named Print Count, Digital Count, and Email Count. These columns will calculate the number of shots for each type.
You can right click these columns and hide them, after you set the formulas. After you enter the formula, you can right click the cell and hit Convert to Column Formula to apply the formula to the entire column.
Print Count's formula is:
=Print@row * (IF(ISBLANK([Shot 1]@row), 0, 1) + IF(ISBLANK([Shot 2]@row), 0, 1) + IF(ISBLANK([Shot 3]@row), 0, 1))
Digital Count's formula is:
=Digital@row * (IF(ISBLANK([Shot 1]@row), 0, 1) + IF(ISBLANK([Shot 2]@row), 0, 1) + IF(ISBLANK([Shot 3]@row), 0, 1))
Email Count's formula is:
=Email@row * (IF(ISBLANK([Shot 1]@row), 0, 1) + IF(ISBLANK([Shot 2]@row), 0, 1) + IF(ISBLANK([Shot 3]@row), 0, 1))
Then in your report you will use the Group, Summary, and Sort buttons to make the data look exactly how you want.
In the screenshot below, I Group by Date and Type, and I have a Summary for Print Count, Digital Count, and Email Count. My summaries all use SUM.
The report will also let you do pretty cool things like filtering, which might be useful in the future. For example, if you wanted to find all of the dates where you need to print at least 10 images of shoes. Or if you want to find dates where you needed to deliver prints, digitals, and emails for handbags all on the same day.
I hope this helps, let me know if you have any questions!
SSFeatures  The browser extension that adds more features into SmartSheet.
 Automatic sorting, sorting with filters, saving sort settings
 Spell checking
 Report PDF generator that supports grouped and summarized reports
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!