0

I need to do a countif that will count every other, odd cell. It needs to countif for exact matches, so if I'm looking for "1", it doesn't also count when a 1 appears in 10, 11, 12, etc.

I've referenced the question linked below, which has gotten me pretty close, but this is outside what I've done in the past, so I'm not quite getting there.

This is what I have now, but it's returning every time a 1 appears:

=SUMPRODUCT(--(MOD(COLUMN($B$6:$BY$6)-COLUMN($B$6)+1,2)=1),ISNUMBER(SEARCH("1",$B$6:$BY$6))+0)

(Converted to a question from here: How to use Excels COUNTIF in every nth cell and retain wildcard functionality)

Community
  • 1
  • 1
Jessica
  • 1
  • 2

2 Answers2

0
=SUMPRODUCT(N(ISODD(COLUMN(myRange))*myRange=1))

where myRange is $B$6:$BY$6

It is counting the cells where 1 appears in the odd columns (E,I,Q). If you prefer the even columns, change ISODD to ISEVEN

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • That worked perfectly! Thank you so much! I am decently advanced in Excel, but I've never had to do this every other cell thing. Usually, I use my own data, so I have clear ranges, but this is something I'm working on for another group, based on their existing data set. – Jessica Aug 31 '16 at 20:13
-1

Well, I can't paste anything in a comment, but here's what I have. The top set of data is how it shows up in the reporting. The bottom is just a test area. The formula counts work except for "1" at the moment. There is only 1 instance of a 1 alone, but the formula is returning 6 because it's counting the instances of 10 and 11. Because my numbers will be going past 11, I need to get it to only count 1's and 2's when they are single numbers.

Screen Capture

Jessica
  • 1
  • 2