# COUNTIF

#### montanaaggie

##### Board Regular
I am trying to do a COUNTIF with a range of cells. I keep getting the error "#VALUE!"

Here is my equation:
=COUNTIF((G16:G23,G25:G34,G36:G40,G42:G45,G47:G48,G50:G53,G55:G57,G59:G62),"Policy")

How should my equation look, so that it will look in those cells referenced and count the number of times a cell is populated with "Policy"

Thanks

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Oaktree

##### MrExcel MVP
You cannot use discontinuous ranges with COUNTIF.

You can either:

1) Add the 8 countifs (1 for each continuous range) together
2) Use =COUNTIF(G16:G62,"Policy") and ensure that the cells you want to omit will not equal "Policy"
3) Use a more complicated SUMPRODUCT formula to account for the excluded cells.

#### jmckeone

##### Well-known Member
it would appear that COUNTIF does not like multiple discontiguous ranges ... try this instead

=(COUNTIF(G16:G23),"Policy")+(COUNTIF(G25:G34),"Policy")+(COUNTIF(G36:G40),"Policy")+(COUNTIF(G42:G45),"Policy")+(COUNTIF(G47:G48),"Policy")+(COUNTIF(G50:G53),"Policy")+(COUNTIF(G55:G57),"Policy")+(COUNTIF(G59:G62),"Policy")

#### montanaaggie

##### Board Regular
Thanks for your help...I decided to make it a discontinued range. It's a long equation, because within those cells there are several words it needs to look for. Oh well, at least it appears to be working.

Thanks again.

#### Brian from Maui

##### MrExcel MVP

ADVERTISEMENT

You can also name the ranges and use,

=SUM(COUNTIF(INDIRECT({"aaa","bbb","ccc"}),"Policy"))

Where aaa, bbb, and ccc are named ranges.

Edit,

If you have the Morefunc addin try,

=SUMPRODUCT(--(ARRAY.JOIN(A1:A3,C1:C3,E1:E3)="policy"))

#### Aladin Akyurek

##### MrExcel MVP
Or, given the regularity in G16:G62...

=COUNTIF(G16:G62,"Policy")-SUMPRODUCT(--(MOD(ROW(G16:G62)-ROW(G16)+1,8)=0),--(G16:G62="Policy"))

#### Oaktree

##### MrExcel MVP
Or, given the regularity in G16:G62...

=COUNTIF(G16:G62,"Policy")-SUMPRODUCT(--(MOD(ROW(G16:G62)-ROW(G16)+1,8)=0),--(G16:G62="Policy"))

Hi, Aladin. Given the differing intervals (11, 6, 5, 3, 5, 4) between "skipped" rows (24, 35, 41, 46, 49, 54, 58) , I don't think the MOD(ROW( approach quite works here. I think the OP would need a ISNUMBER(MATCH(ROW( etc. formula in the SUMPRODUCT, though you've certainly worked magic before...

#### Aladin Akyurek

##### MrExcel MVP
Or, given the regularity in G16:G62...

=COUNTIF(G16:G62,"Policy")-SUMPRODUCT(--(MOD(ROW(G16:G62)-ROW(G16)+1,8)=0),--(G16:G62="Policy"))

Hi, Aladin. Given the differing intervals (11, 6, 5, 3, 5, 4) between "skipped" rows (24, 35, 41, 46, 49, 54, 58) , I don't think the MOD(ROW( approach quite works here. I think the OP would need a ISNUMBER(MATCH(ROW( etc. formula in the SUMPRODUCT, though you've certainly worked magic before...

Did I miscount? I thought every 8th should be skipped starting off the first 8th cell, not off G16 itself.

Replies
6
Views
118
Replies
0
Views
102
Replies
6
Views
883
Replies
7
Views
359
Replies
0
Views
137

Threads
1,136,369
Messages
5,675,362
Members
419,565
Latest member
Phil57

### 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

### 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