Best answer

Advanced Excel formulas

  • 10 January 2023
  • 4 replies
  • 100 views

Hello everyone. 

I need a hand with creating automated sales reports.

There is much information I include in weekly reports, but let me walk you through one metric. The process goes like this:

  1. All leads that end up in a CRM are also recorded in an Excel sheet (this is automated with Zapier). The information included: first and last name, email address, and date.
  2. On a weekly basis I create a report where I summarize all new leads and add a number to the report. This is done manually, I go to the Excel sheet, CTRL+F, type the date and find how many new leads ended up in a Sheet on a specific date. This is basically the COUNTIF formula in Excel, but I would like to automate it with Zapier.

How can I automate the 2nd step? 

 

Thank you!

icon

Best answer by Troy Tessalone 12 January 2023, 16:45

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Userlevel 7
Badge +14

Hi @Tanya1992 

Good question.

Can you please share more details about which date is being used in Step 2?

What day do you do this on a weekly basis?

Would be best to outline a specific example and possibly include some screenshots for context.

Hey Troy, maybe this video will give you all the answers :)

 

Userlevel 7
Badge +14

@Tanya1992 

Honestly, you’re probably better off configuring dynamic formulas in GSheets to do the lookup calculations.

By dynamic, I mean a formula that can reference a date (so you can change for each day), and so that you can copy/paste into similar cells but have the formula adjust based on the lookups being done.

 

Make sure to review the available help articles for using GSheets in Zaps: https://zapier.com/apps/google-sheets/help

Especially this one for how a GSheet must be configured to be used in Zaps: https://help.zapier.com/hc/en-us/articles/8495978765965

This is important because of how your report is laid out with gap rows/columns/cells and merged cells.
 

Thank you, Troy, this was very helpful! :) I will give it a try.