Dynamic fomula for Month, Qtr and YTD

This is what I have so far.

For the months array enter this formula in B2 an fill across.

Formula:

=INDEX($B$9:$S$12,MATCH($A$1,$A$9:$A$12,0),SMALL(IF(B$1=$B$8:$S$8,COLUMN($B:$S)-MIN(COLUMN($B:$S))+1),MONTH($A2&0)))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

For quarters array entered in B3 and fill across.

Formula:

=SUM(N(INDEX($B$9:$S$12,MATCH($A$1,$A$9:$A$12,0),N(IF(1,SMALL(IF(B$1=$B$8:$S$8,COLUMN(INDEX(1:1,MATCH($A3,$B$6:$S$6,0)):INDEX(1:1,(MATCH($A3,$B$6:$S$6,0)+17)))),{1,2,3}))))))

The YTD I haven't done, yet.

Hi 4gurus,

Been working on some alternative formulas.

The YTD thing is throwing me so the formula in row 4 is a work in progress.

I've taken the liberty of inserting two helper rows above the source data.

The reason being merged cells cause havoc for formulas. The helper rows are a way out.

In B6 filled down and across to S7 of the attached this helper row formula.

Formula:

=LOOKUP("zzzzzzz",$B8:B8)

Are we OK so far?

@ John,

I like it.

In fact I borrowed your idea and extended it a bit to the Data Validation lists and the original month row (9).

I am kind of stuck right now on how to explain simply what I've done though. Quite a few changes. I am afraid I will unnecessarily risk introducing confusion.

I would suggest a slight change to your formula. OP wants YTD to be all

what kind of edit should I make in the Qtr formula so that I get the sum of Qtrs spend, loss, and save for all the people instead of individual.

Removing the name ($A$11:$A$14=$A$1) criteria does the trick.

=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*(MONTH($B$7:$S$7)<=MONTH(TODAY())))


Thanks for the backup and assist. It's appreciated. I love team-builds.

In W1 to W4 add "Qtr" start dates

01/01/2016
01/04/2016
01/07/2016
01/10/2016

For Qtr team total:

Spend

=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=VLOOKUP($A$3,$V$1:$W$4,2,0))*($B$9:$S$9<=EOMONTH(VLOOKUP($A$3,$V$1:$W$4,2,0),2)))

Copy across for "Save" & "Loss"

See table in A17 etc

OR

this removes need for W1:W4 data



=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,(RIGHT($A3)+0-1)*3+1,1))*($B$9:$S$9<=EOMONTH(DATE(2016,(RIGHT($A3)+0-1)*3+1,1),2)))

You have these formulae: in B2 to D2 and B5 to D5

formula from B2

=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,MONTH($A$2&1),1))*($B$9:$S$9<=EOMONTH(DATE(2016,MONTH($A$2&1),1),0)*($A$11:$A$14=$A$1)))

from B5

=SUMPRODUCT(($B$11:$S$14)*($B$10:$S$10=B$1)*($B$9:$S$9>=DATE(2016,MONTH($A$2&1),1))*($B$9:$S$9<=EOMONTH(DATE(2016,MONTH($A$2&1),1),0)))

You are welcome. Thank you for the feedback.