Sum data based on one criteria and two criteria.

[Frick]

This project is progressing and almost complete. I have attached a workbook with VBA code, much of which contributed and many thanks to TMS. You rock.

The last piece of this exercise is the aggregating the accrual amounts based on the reference criteria in Col A, and with one exception including a criteria in Col D in the DATA2 worksheet. The reference #'s in Col A provide for matching and summing the amounts into the table K2:L13.

It all works fine except for the exception. The reference number in Col A: 0160A1 must be broken into two amounts based on certain criteria found in cells in Col D where their is a starting SP. Only the SP is relevant as the rest can be anything.

So, the 0160A1 SP is the criteria for the sum. Note on DATA tab I have highlighted the rows that should form the 2nd grouping for totaling.

I was thinking since we already have the formula to get the total for 0160A1 perhaps using the existing formula minus a similar formula adding in that second criteria will get what is needed. Just a thought.

[Limor_OP]

Hi,
perhaps you can use something like that (if I understand you correct):

=SUM(IF(ISNUMBER((SEARCH("0160A1",A2:A400))*(SEARCH("sp",A2:A400))),1,0)*(D2:D400))

This will sum up all the items containing '0160A1' / 'SP'

[Frick]

That formula works on DATA tab. However, I need to include something like that into the formula in cell K2 on DATA2 tab. This is where I am aggregating and summing the totals. The formula in K2 is:

=LET(lr, COUNTA($A:$A)+3, f, $A$2:INDEX($A:$A, lr), l, $I$2:INDEX($I:$I, lr), u, UNIQUE(f), i, INDEX(m, MATCH(u, f, 0)),s, SUMIF(f, u, l), h, CHOOSE({1,3},u,i,s), h)

This formula populates down including all the various criteria's in Col A

I have been trying your formula and something similiar trying to get into the existing formula without success so far.

[wk9128]

ANS. POST$1
pls try this formula

Please Login or Register  to view this content.