Sum Function

[hiteshchouhan]

Hi All,

I am using the below array function to count the number of weeks which are meeting the criteria of >=100, from last 4 weeks which have scores and ignore the weeks containing 'NA'. However it is not giving me the desired answer.

The formula is working fine if the criteria is <100 but when I change the condition it gives me false results that too different for each row.

Formula:
=SUM((IF(IF(COLUMN(H2:X2)>=LARGE(IF(H2:X2<>"NA",COLUMN(H2:X2)),MIN(COUNT(H2:X2),4)),IF(H2:X2<>"NA",H2:X2))>=100,1,0)))

Data Set:

WK1 WK2 WK3 WK4 WK5 WK6 WK7 WK8
100 95 154 NA NA 111 124 NA

So the answer should be 3 (Meeting for WK7, WK6 & WK3) however it is giving me 8.

Can anybody please help me with this.

Regards,
Hitesh

 

[ncrcnbl]

I think you want COUNTIFS, not add up the numbers > 100
=COUNTIFS(A2:G2,"<>#N/A",A2:G2,">100")

 

[ncrcnbl]

When you enter COUNTIFS, the range is what you want to count, the criteria is in quotation marks, COUNTIFS is different than COUNTIF because you can enter multiple criteria.

 

[hiteshchouhan]

Yes! I want countifs for WK# > 100, but with Last 4 worked, ignoring the NA. Suppose if Score in Last 2 week is 100 & 99 and then 3rd Last is 'NA' then formula must ignore the 3rd Week and Take the Previous week

 

[hiteshchouhan]

Actual function is this: =SUM((IF(IF(COLUMN(H2:X2)>=LARGE(IF(H2:X2<>"NA",COLUMN(H2:X2)),MIN(COUNT(H2:X2),8)),IF(H2:X2<>"NA",H2:X2))<100,1,0)))

Which count the number of non-meeting weeks starting from the X2. The number of weeks which should be count is based on above red digit (8).

So say suppose if from a set of 14 weeks from WK1 to WK14, an individual worked on only 10 weeks (Not worked on WK2, WK3, WK10 and WK12) then above formula will count from the week WK14, WK13, WK11, WK9, WK8, WK7, WK6, WK5 and WK4 only (Last 8 weeks having score). Now if he is not meeting the Target of 100 for WK14, WK11 & WK9 then the final answer would be 3.

I just want to use the same formula for >=100 instead of <100 but results are not what it should be.

Can anyone please help.

Thanks in advance...