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

RSanders

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

 

 

 

Paul Newcome

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

thank you that worked well

 

 

Paul Newcome

Excellent! Happy to help.