hiteshchouhan

New Member
Joined
Apr 11, 2018
Messages
7
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ncrcnbl

New Member
Joined
Jul 14, 2018
Messages
24
I think you want COUNTIFS, not add up the numbers > 100
=COUNTIFS(A2:G2,"<>#N/A",A2:G2,">100")
 

ncrcnbl

New Member
Joined
Jul 14, 2018
Messages
24
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

New Member
Joined
Apr 11, 2018
Messages
7
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

New Member
Joined
Apr 11, 2018
Messages
7
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,566
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top