CODEWITHSUNDEEP
excelexcel-formula
Deke
Deke

Reputation: 495

Excel Formula that uses certain criteria then subtract from value?

I'm trying to figure out a way to write a formula where it looks for a certain catid in column C (in this case "DD") and then subtract retone (Column D) from each retone for the other catid's. See examples below:

Before This is before the formula

After This is what I'd like to end up with

The other issue is I need to make sure that I'm keeping it within the same pack number (so I only want results for the catid as long as the packnumbers are the same within those catid's).

I'm also not opposed to writing this in VBA but I run into the same issue, I'm just not sure how to write the logic so it recognizes the catid under "DD" and RetOne to subtract from the other catid's and only report on pack's that are the same number. ("DD" will not always be at the top of the grouping it could be anywhere in the grouping)

Any help or push in the right direction would be greatly appreciated. Thanks! -Deke

Upvotes: 0

Views: 359

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

If DD is only used once per PackNumber then use SUMIFS

=SUMIFS(D:D,A:A,A2,C:C,"DD") - D2

enter image description here

Or using the Dynamic Array formula FILTER() if one has that:

=@FILTER(D:D,(A:A=A2)*(C:C="DD"))-D2

enter image description here

Upvotes: 2

Related Questions