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

✭✭✭
edited 12/09/19

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:

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!