# Sum Function

#### hiteshchouhan

##### New Member
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.

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

#### ncrcnbl

##### New Member
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
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
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.

Replies
5
Views
160
Replies
3
Views
121
Replies
4
Views
504
Replies
5
Views
91
Replies
4
Views
448

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.

### Which adblocker are you using?

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

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