COUNTIF

[montanaaggie]

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

 

[Oaktree]

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]

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]

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]

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]

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]

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]

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.