Subtractifs

[keith740]

Hello Forum,

I know excel doesn't have a subtractifs, but that's what im shooting for.

I have 8 tables representing each hour descending column A:B

I need to find the difference in $ each hour

thing is the agents in column A are not static and will change order each hour, so will need to be an IF formula.

any help is greatly appreciated.

[FDibbins]

Are you OK with using a helper column?
(otherwise I can come up with an ugly OFFSET() formula, if you want)

[keith740]

helper columns are fine... i have tons allready lol

[FDibbins]

This if the OFFSET formula...
=INDEX(OFFSET($A$1,(INT(ROWS($A$1:A1)/$G$1)+1)*12+1,1,10,1),MATCH(M14,OFFSET($A$1,(INT(ROWS($A$1:A1)/$G$1)+1)*12+1,0,10,1),0))-INDEX(OFFSET($A$1,(INT(ROWS($A$1:A1)/$G$1))*12+1,1,10,1),MATCH(M14,OFFSET($A$1,(INT(ROWS($A$1:A1)/$G$1))*12+1,0,10,1),0))
(we will need to fiddle with this if your table does not start in row 1)

[FDibbins]

hmm perhaps my OFFSET formula may be a better approach here, considering all the messing around...
The alternative is to add a helper column - which you can hide if you want - (I used C), and use this approach...
C1 =A1 (this is teh starting "seed"
C2=IF(LEFT(A2,4)="Hour",A2,C1)
copied down

Then put Hour2, Hour3 etc in your summary table
another helper column (I used L)
L13=IF(LEFT(M13,4)="Hour",M13,L12)
copied down

For the extract...
N14=INDEX($B$1:$B$100,MATCH(L14&M14,INDEX($C$1:$C$100&$A$1:$A$100,0),0))-INDEX($B$1:$B$100,MATCH(LEFT(L14,4)&RIGHT(L14,1)-1&M14,INDEX($C$1:$C$100&$A$1:$A$100,0),0))
copied down

[FlameRetired]

What about

=SUMIF($A$14:$A$23,M14,$B$14:$B$23)-SUMIF($A$2:$A$11,M14,$B$2:$B$11)

in N14 and down, and

=SUMIF($A$26:$A$35,M26,$B$26:$B$35)-SUMIF($A$14:$A$23,M26,$B$14:$B$23)

in N26 and down?

Edit Typos on cell addresses.

[FDibbins]

I was trying to make something that could just be copied all teh way down

[FlameRetired]

Hmm. I missed the "mission" ... again.

[FDibbins]

Actually, you made me re-think my 2nd formula a bit to this...
=SUMIFS(B:B,C:C,L14,A:A,M:M)-SUMIFS(B:B,C:C,LEFT(L14,4)&RIGHT(L14,1)-1,A:A,M:M)

[keith740]

Awesome, got it! Ty again Guys...

Im petitioning that MS add SUBTRACTIFS function!

[FDibbins]

Good luck with that

Glad we could help, thanks for the feedback

[FlameRetired]

Ditto.____________