Formula to populate cell based on multiple column/row results

[KayJay0618]

I don't have a clue how to search for something to help me with this problem. I have a worksheet with 5 columns (num, FY-Qtr, OP, UP, Qtr Result). I need to populate the Qtr Result column with the word Keep IF the total of the OP and UP columns for an FY-Qtr and Num is greater than zero. If there is nothing but 0 in the OP and UP columns for a Num and FY-Qtr I need to populate the Qtr Result column with Delete. I've attached a sample. You can see that for num 4071151, there are numbers greater than zero on all 5 rows for FY15-Q3 so I would want to populate column E with Keep on all 5 of those rows. I would also want Keep in column E for all 6 rows for FY15-Q4 because 2 of the rows have numbers greater than 0 in the OP or UP column. But column E for all of the FY16-Q1 rows for num 4071151 should be populated with the word Delete because none of the OP/UP columns for FY16-Q1 have numbers greater than 0. I have several thousand records I need to resolve QUICKLY and I just can't seem to figure this one out. I'm thinking it's some sort of sumproduct formula but I'm really struggling with figuring it out and maybe I'm off track. Thank you in advance!


[TABLE="width: 418"]
<tbody>[TR]
[TD]Num

[/TD]
[TD]FY-QTR

[/TD]
[TD]OP

[/TD]
[TD]UP

[/TD]
[TD]Qtr Result

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q3

[/TD]
[TD]0

[/TD]
[TD]35.68

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q3

[/TD]
[TD]0

[/TD]
[TD]66.45

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q3

[/TD]
[TD]0

[/TD]
[TD]31.17

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q3

[/TD]
[TD]0

[/TD]
[TD]31.17

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q3

[/TD]
[TD]0

[/TD]
[TD]31.17

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]31.17

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]31.17

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY15-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q1

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q1

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q1

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4071151

[/TD]
[TD]FY16-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4066704

[/TD]
[TD]FY14-Q3

[/TD]
[TD]0

[/TD]
[TD]63.83

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4066704

[/TD]
[TD]FY14-Q3

[/TD]
[TD]20.19

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4066704

[/TD]
[TD]FY14-Q3

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4066704

[/TD]
[TD]FY14-Q3

[/TD]
[TD]25.7

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q2

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q3

[/TD]
[TD]0.21

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q3

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q3

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Keep

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete

[/TD]
[/TR]
[TR]
[TD]4065758

[/TD]
[TD]FY14-Q4

[/TD]
[TD]0

[/TD]
[TD]0

[/TD]
[TD]Delete
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Assuming data starts in A1, in E2:

=IF(SUMIF(B:B,B2,C:C)+SUMIF(B:B,B2,D:D)>0,"Keep","Delete")

 

[KayJay0618]

Unfortunately this does not work. Column A is the customer number, Column B is the FY-Qtr. If column C (OP) is > 0 there is an overpayment and if column D (UP) is > 0 there is an overpayment. For every customer number there is a potential for multiple rows representing up to 10 FY-Qtrs each of which could have multiple rows. In my sample customer 4071151 has 4 FY-Qtrs. FY15-Q3 has 5 rows, all of which have an underpayment (UP) so I would need to KEEP all 5 of those rows. FY15-Q4 has 6 rows, two of which have amounts >0 in the UP column, so I would need to keep ALL 6 rows for FY15-Q4. FY-16-Q1 has 3 rows, none of which have an OP or UP so I need to DELETE all 3 of those rows. Perhaps this will better explain what I'm trying to do:

For those rows where the values in columns A and B are the same AND the sum of the values in column C is >0 OR the sum of the values in column D is >0 then Keep
For those rows where the values in columns A and B are the same AND the sum of the values in columns C =0 AND the sum of the values in column D =0 then Delete

 

[Marcelo Branco]

Maybe something like this

E2 copied down
=IF(SUMPRODUCT(--(A$2:A$1000=A2),--(B$2:B$1000=B2),C$2:C$1000+D$2:D$1000),"Keep","Delete")

M.

 

[KayJay0618]

That didn't work. However, between your two answers I was able to get over my blocked state and figured out a way to do it. Thank you!

 

[Marcelo Branco]

That didn't work. However, between your two answers I was able to get over my blocked state and figured out a way to do it. Thank you!

It works, for sure!

Just for the records


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Num​

[/TD]
[TD]

FY-QTR​

[/TD]
[TD]

OP​

[/TD]
[TD]

UP​

[/TD]
[TD]

Qtr Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

35,68​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

66,45​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

31,17​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

31,17​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

31,17​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

31,17​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

31,17​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY15-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

4071151​

[/TD]
[TD]

FY16-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

4066704​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

63,83​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

4066704​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

20,19​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

4066704​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

4066704​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

25,7​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

0,21​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Keep​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD]

4065758​

[/TD]
[TD]

FY14-Q4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Delete​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in E2 copied down (same formula as post 4)
=IF(SUMPRODUCT(--(A$2:A$1000=A2),--(B$2:B$1000=B2),C$2:C$1000+D$2:D$1000),"Keep","Delete")

M.

 

[KayJay0618]

I'll try it again tomorrow but when I tried it the other day I received #VALUE .

 

[Marcelo Branco]

If the formula produced a #VALUE error i suspect there is (are) some cell(s) in the C or D columns that contains a text, not a number.

M.