How to count several non-blank rows based on specific criteria across two sheets

emka

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!

SSFeatures

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!