SUMIFS with INDEX and MATCH needs an update.

[Ioweyouone]

The formula below is used in a sheet named ‘All Data’ and references another sheet named ‘Jobs Data’.

=SUMIFS(INDEX('JOBS DATA'!E:J,0,MATCH($C$5,'JOBS DATA'!$E$4:$J$4,0)),'JOBS DATA'!A:A,">="&$C$4,'JOBS DATA'!A:A,"<="&EOMONTH($C$4,0),'JOBS DATA'!Q:Q,B7)

The formula works well and returns a number based on the dates in ‘Jobs Data’! A:A, regardless of the word selected in the drop down ‘All Data’! C5.

The NEW objective is to continue using column ‘Jobs Data’! A:A when the word “forecast” is selected in ‘All Data’!C5. I would like to expand the formula so If the user selects “actual” in the C5 dropdown, the formula will then search the dates in column ‘Jobs Data’! K:K , instead of ‘Jobs Data’! A:A. and return the appropriate value. Thanks.

‘All Data’!C4 is the first day of the month.

‘All Data’!C5 is a dropdown cell with a choice of “forecast” or “actual”.

‘Jobs Data’! A:A is a column of dates.

 

[Fluff]

Try

=SUMIFS(INDEX('JOBS DATA'!E:J,0,MATCH($C$5,'JOBS DATA'!$E$4:$J$4,0)),IF($C$5="Actual",'JOBS DATA'!K:K, 'JOBS DATA'!A:A),">="&$C$4,IF($C$5="Actual",'JOBS DATA'!K:K, 'JOBS DATA'!A:A),"<="&EOMONTH($C$4,0),'JOBS DATA'!Q:Q,B7)

 

[Ioweyouone]

Fluff,
Worked great, thanks. I'll mark as solved.

 

[Fluff]

You're welcome & thanks for the feedback.