Formulas for counting number of types of rows for different months/ financial years
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
Comments
-
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.
-
thank you that worked well
-
Excellent! Happy to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!