COUNTIF

montanaaggie

Board Regular
Joined
Nov 11, 2005
Messages
110
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
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
Joined
Jun 3, 2006
Messages
550
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
Joined
Nov 11, 2005
Messages
110
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
Joined
Feb 16, 2002
Messages
8,458

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
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

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

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