Count the number of times a particular word appears in every nth column

Roxyz72

New Member
Joined
Sep 14, 2023
Messages
6
Office Version
  1. 2021
Platform
  1. Windows
I have a text-based dataset that follows strict patterns. I want to see how many times a particular word - "Noun", for example - appears in every 5th cell for each row. "Noun" also appears in other cells that I don't want to include in the count, or I'd use COUNTIF for each row. Also, other words may appear in the cell (e.g. "Verb"), which I will count later, once I've figured out the formula. Each row starts in A column, but I started the array in C column because that's where the first instance of "Noun" that I want could be, so I figured I should count from there. I tried the following:
{=SUM(--(MOD(COLUMN($C2:$PC2),5)="Noun"),--($C2:$PC2="Noun"))}
but it seems to count all of the instances of "Noun" in that particular row, while
{=SUMPRODUCT((MOD(COLUMN(C2:PC2),5)="Noun")*($C2:$PC2))}
didn't work at all. I've tried several other variations as well, too many to write down here (those were just the most recent), but none of them returns the correct value.
In one sentence: I'd like to count all of the times that the word "Noun" appears in every 5th cell of each row in the dataset.
Any help would be appreciated.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
How about
Excel Formula:
=COUNT(FILTER(COLUMN(C2:PC2),(C2:PC2="noun")*(MOD(COLUMN($C2:$PC2),5)=3)))
 
Upvote 0
Solution
Try this:
1694717745517.png
 
Upvote 0
How does that have any bearing on what the OP asked?
 
Upvote 0
How about
Excel Formula:
=COUNT(FILTER(COLUMN(C2:PC2),(C2:PC2="noun")*(MOD(COLUMN($C2:$PC2),5)=3)))
Seems to work perfectly - I thought it should be a COUNT function, but everywhere I looked online said COUNT couldn't be used with every nth cell. Thanks for this!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback.
One quick question - I get everything in the formula except the "=3" at the end, which has me a bit bewildered. Could you explain that a bit for me?
 
Upvote 0
Because you want every 5th column starting at col C (3) you need the modulus to equal 3
Fluff.xlsm
ABCDEFGHIJKLM
11234012340123
Data
Cell Formulas
RangeFormula
A1:M1A1=MOD(COLUMN(A2:M2),5)
Dynamic array formulas.
 
Upvote 0
Because you want every 5th column starting at col C (3) you need the modulus to equal 3
Marvelous! Wish someone on other websites had explained that little detail. Thanks again!
 
Upvote 0

Forum statistics

Threads
1,215,073
Messages
6,122,977
Members
449,095
Latest member
Mr Hughes

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
Back
Top