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.