Formulas for counting number of types of rows for different months/ financial years

Options
RSanders
RSanders ✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, 

I currently have a sheet where we follow up leads / quotes and am wanting to create some formulas, either on the same sheet or in another sheet (so user doesn't see all formulas). 

 

I have the following columns (just a few of what i have):

Status (which has quotation won, quotation abandoned, quotation lost) etc. 

Date (dd/mm/yyyy)

Value

Quoter (their name)

 

I am trying to get some overall figures for financial years (with out having to do parent rows). of number of quotes won and so on in the financial year (01/07/2018 to 30/06/2019)

Then a column with the quote total for that financial year for each status

I am also trying to do that on a month by month or week by week basis so how many quotes have been won by the quoter in the month of october for the 18/19 finanical year. 

I was considering do checkbox colums for each financial year, then trying to do a formula that if the date column is between certain dates that will tick, then a month column (to give month number) then utilsing those columns to make the formulas with a count ifs formula (count ifs 18FY is checked and 'Stan' is in quoter column and status is 'quotation won', etc) then a sum ifs for the value formula i need. However, am not quite sure how to set all of this up.

 

We will then be creating a dashboard with charts that will be reviewed by management at a weekly department meeting and comparing years/months/quoters etc. - I know how to do this, but am just having trouble with the formulas.

 

Is anyone able to help me out? 

 

thanks heaps

 

 

 

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    There are a few different ways to set this up. The easiest would be to just use a COUNTIFS or SUMIFS formula. If you are breaking the summary down into separate rows for each criteria as below...

    Name          Status         FY          Count          Total

    John             Won            18/19

    John             Lost            18/19

    Steve            Won            18/19

    Steve            Lost             18/19

     

    For the count you could use

     

    =COUNTIFS([Name Column Name]:[Name Column Name], Name@row, [Status Column Name]:[Status Column Name], Status@row, [Fiscal Year Column Name]:[Fiscal Year Column Name], >= DATE(2018, 7, 1), [Fiscal Year Column Name]:[Fiscal Year Column Name], <= DATE(2019, 6, 30))

     

    You would just change the column name or use x-sheet references, and of course adjust the dates as needed.

     

    For the total you could use

     

    =SUMIFS([Value Column Name]:[Value Column Name], [Name Column Name]:[Name Column Name], Name@row, [Status Column Name]:[Status Column Name], Status@row, [Fiscal Year Column Name]:[Fiscal Year Column Name], >= DATE(2018, 7, 1), [Fiscal Year Column Name]:[Fiscal Year Column Name], <= DATE(2019, 6, 30))

     

    It is exactly like the COUNTIFS formula except you add the Value column to total up based on the same exact criteria.

  • RSanders
    RSanders ✭✭✭
    Options

    thank you that worked well :)

     

     

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!