-3

I need a formula to:

  1. recognize the quarter for each date
  2. divide the revenue in half for the start and end quarter.

Below is an example of the results I'm looking for. Need this to be a formula I can build in SmartSheet and/or Excel and transfer over to a SmartSheet.

Example of results I'm looking for:

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
jotalley
  • 1

1 Answers1

0

Add a "helper row" with quarter number and year so you can use a SUMIFS/2

=(SUMIFS($[Est Revenue]@row, $[Start Date]@row, IFERROR((ROUNDUP(MONTH(@cell) / 3) + "" + YEAR(@cell)), 0) = [Q1 2020]$1) + SUMIFS($[Est Revenue]@row, $[End Date]@row, IFERROR((ROUNDUP(MONTH(@cell) / 3) + "" + YEAR(@cell)), 0) = [Q1 2020]$1)) / 2

The helper row would be quarter number before the 4 digit year. So 12020 would be quarter 1 2020.

kevinfansler
  • 82
  • 2