Issues with dragging formula down after multiple range of cells are referenced

[Farhasnat]

I have made a formula in column D that evaluate a range of cells and inputs the value 0 or 1. However, I the formula to be applied to more than 50,000 rows and manually doing that is not efficient. The formula actually spills itself since I used a range of value as reference. Any help to sort of apply the formula to all the cells would be very helpful.

Essentially what I want to achieve with this formula is that if the number of independent directors (INDEP_DIRS/COLUMN C) is greater or equal to 3 in ALL of the years from 1995-2003 for a TICKER then it should show 1 otherwise 0. With this condition if you have any other formula suggestion would be great as well.

Thanking you in advance.

[Glenn Kennedy]

??

Is this what you want?

=SUMIF(B:B,M2,C:C)

copied down.

[Farhasnat]

Thank you for your reply.

My apologies I should have been more clear my formula is COLUMN D is what I want which is returning the value of 1 if for ALL of the years (1995-2003) has values in COLUMN C >= 3 for each of the TICKERS (COLUMN A) - if yes then it should return the value of 1 otherwise 0.

I have corrected my original post accordingly.

[Glenn Kennedy]

=COUNTIFS(A:A,A2,C:C,">=3")

If not, please add some expected results.

[Farhasnat]

Thanks but I am not counting anything here, I am returning the value of 0 or 1 based on condition. The expected the results and the formula I am using is already there in the sheet (COLUMN D) which I have already mentioned.. The issue here is I can't drag the formula cause its a spill

[FlameRetired]

This won't spill. If I understand correctly try this in D2 and double click the fill handle to copy all the way down.

Please Login or Register  to view this content.

Even over 50,000 + rows this should be lightning fast.

[Farhasnat]

Thank you this exactly gets me somewhere what i want. But the the formula returns 1 in ANY year COLUMN C is >= 3. However, I am actually trying to get to the point where the formula should return 1 IF AND ONLY IF all of the YEARS from 1995-2003 has corresponding value of 3>= in COLUMN C.

I have attached the file where i used the formula you advised

[Glenn Kennedy]

Then try:

=IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,C:C,">=3"),1,0)

If not, upload a MUCH smaller file showing expected answers.

[HariArvi]

Hi,
Try this.

=IFERROR(IF(AND(MATCH(B2,$M$2:$M$10,0)>0,C2>=3),1,0),0)

[FlameRetired]

Thank you this exactly gets me somewhere what i want. But the the formula returns 1 in ANY year COLUMN C is >= 3. However, I am actually trying to get to the point where the formula should return 1 IF AND ONLY IF all of the YEARS from 1995-2003 has corresponding value of 3>= in COLUMN C.

I have attached the file where i used the formula you advised

Sorry. I misunderstood. In that case try replacing that formula with

Please Login or Register  to view this content.

FYI: This part of the formula INDEX(FREQUENCY(C2,3-1/10^7),2) will take care of the >=3 part since the second 'bin' 2 in FREQUENCY will count all values greater an amount slightly less than 3. This would include 3.

[Farhasnat]

Thank you so much. This performs exactly as I wanted. Appreciate your help

[Farhasnat]

Thank you. This formula still sort of picks up return value of 1 even if COLUMN C has >= for single year ONLY. But thank you again for your help

[Glenn Kennedy]

Thank you so much. This performs exactly as I wanted. Appreciate your help

Who are you talking to???

[Farhasnat]

Who are you talking to???

You actually, probably my tagging wasn't right. The last formula you have provided got me exactly what i wanted. Thank you again

[AliGW]

You didn't do any tagging. All you need to do in future is mention the member at the start of your post (e.g. @Glenn Kennedy), then it's clear as glass.