If condition for sum duplicate number

Hi Team,

The queries is continue.

I have attached the one sheet and i have put formula in column "C".

The formula is calculating if the hours in column "D" is =>120 then calculate as "1" & below 0 calculate as "0" and otherwise divide hours in column 'D" with "160" for USA & Philippines and for India "180".

The issue for only the duplicate number (A9 to A11) if you check column D9 to D11 the hour total is 158 but the formula calculated sum 0.9875 in column C9 to C11.

As my condition is if total hours is =>120 then it should calculated as "1". So can we modify this formula to calculate the total "1" for duplicate number if it is >=120?

Thanks,
Rahul

Originally Posted by chrispittelli

One work around would be to add some helper columns

https://www.excelforum.com/showthread.php?t=1040120



---------------------------

Hi,

Thanks for reply.

In your formula duplicate number total is "3" column (C9 to C11) but i want this total to be "1".

My goal is if duplicate number total is more than 120 then formula should calculate "1" but not in each duplicate line the total of duplicate line should be "1".

Example- i have 4 duplicate line and its total is 150 (More than 120) then i want formula calculate "0.25" in each line so my total will be "1".

Thanks,
Rahul

This seems to work. In E5 and down.

=IF(COUNTIF($A$5:A5,A5)=COUNTIF($A$5:$A$51,A5),IF(SUMIFS($D$5:D5,$A$5:A5,A5)>=120,1,IF(SUMIFS($D$5:D5,$A$5:A5,A5)<0,0,SUMIFS($D$5:D5,$A$5:A5,A5)/IF(OR(B5={"USA","Philippines"}),160,IF(B5="India",180)))),"")

Does this also mean that when the totals for duplicates are < 120 you still want to sum them and apply the division rule?

Like this:

=IF(COUNTIF($A$5:A5,A5)<=COUNTIF($A$5:$A$51,A5),IF(SUMIFS($D$5:$D$51,$A$5:$A$51,A5)>=120,1,IF(SUMIFS($D$5:$D$51,$A$5:$A$51,A5)<0,0,SUMIFS($D$5:$D$51,$A$5:$A$51,A5)/IF(OR(B5={"USA","Philippines"}),160,IF(B5="India",180)))))

OK.

Let's see if I have the concept now.

=IF(COUNTIF($A$5:A5,A5)<=COUNTIF($A$5:$A$51,A5),IF(SUMIFS($D$5:$D$51,$A$5:$A$51,A5)>=120,1/MAX(COUNTIF($A$5:$A$51,A5)),IF(SUMIFS($D$5:$D$51,$A$5:$A$51,A5)<0,0,SUMIFS($D$5:$D$51,$A$5:$A$51,A5)/IF(OR(B5={"USA","Philippines"}),160,IF(B5="India",180)))))

I am confused.

A19 is a duplicate of A20 and the total hours are 158. 158 > 120 resulting in 1. So shouldn't each of those be 0.5? (Posts 1, 4, 6 and 8.)

In C21 the total is 76 but it is "Philippines" and less than 120 so shouldn't that be divided by 160? (Post 1)

OK.

What can you tell us about C9, C10 and C11? A9, A10 and A11 are duplicates. Each of the numbers column D are < 120 but they total > 120.

Maybe this?

=IF(D5>=120,1,IF(D5<0,0,IF(OR(B5={"USA","Philippines"}),D5/160,IF(B5="India",D5/180,INDEX(INDEX($D$5:$D$51,MATCH(1,1/(A5=$A$5:$A$51),0)):INDEX($D$5:$D$51,MATCH(2,1/(A5=$A$5:$A$51),1))/SUM(INDEX($D$5:$D$51,MATCH(1,1/(A5=$A$5:$A$51),0)):INDEX($D$5:$D$51,MATCH(2,1/(A5=$A$5:$A$51),1))),COUNTIF($A$5:A5,A5))))))

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

@ rchure,

You are welcome. Glad to hear this one works. Thank you for the feedback.