Goal Seek or What if Analysis to be used in order not to calculate the total revenue beyond certain date

[VKiprijan]

Hi Folks,

I need a two-way solution either in Goal Seek or in What if Analysis that WILL NOT calculate the total sum of revenue in the cell E15 if the dates in the cell B2 and B3 are between 01-Sep-2023 and 31-Dec-2023 for example. Also, if you can provide the formula in cell E15 that will do this will be great.

Thank you in advance.

Book1.xlsx
	A	B	C	D	E
1
2	Beginning Date	01-09-23		Department	Company X
3	Ending Date	31-12-23		Finance	1270
4				HR	3560
5				IT	2450
6				Facilities	1350
7				Operations	4200
8				Legal	4450
9				Commercial	6310
10				Projects	7570
11				Logistics	3890
12				Maintenance	5650
13				Transport	5260
14
15				Total Revenue	45960
Sheet1

Cell Formulas
Range	Formula
E15	E15	=SUM(E3:E13)

 

[SanjayGMusafir]

Try -

=IFERROR(SUMIFS(E3:E13,B2,"<"&DATE(2023,9,1),B3,">"&DATE(2023,12,31)),"Check dates")

 

[SanjayGMusafir]

Try -

=IFERROR(SUMIFS(E3:E13,B2,"<"&DATE(2023,9,1),B3,">"&DATE(2023,12,31)),"Check dates")

The above gives error. Use this one -

=IF(AND(B2<DATE(2023,9,1),B3>DATE(2023,12,31)),SUM(E3:E13))

 

[VKiprijan]

The above gives error. Use this one -

=IF(AND(B2<DATE(2023,9,1),B3>DATE(2023,12,31)),SUM(E3:E13))

This second formula works well. Thanks a lot.

 

[SanjayGMusafir]

Glad to help you