Looking to cout every 'nth' row that doesn't contain a specific string

Hi there, I have an excel sheet that contains data for every employee and the dates in which their training modules should be complete, when certain training modules are up for renewal the cell will present the string "1 MTH DUE !" or if this training module isn't necessary for that employee it will contain "NA".

at the bottom of the sheet i have the total modules for the staff and the total modules completed, the total modules completed will take away any of the cells referring to 'na' or '1 MTH DUE!' I've done this using a very long formula which isn't efficient especially when adding new members of staff to the matrix.

I've explored for hours online and looked into sumproduct formulas but i just can't seem to get it to work.

So basically the goal is to count every second row starting from n7 down to n17 for now and return the amount of staff that aren't due or dont have NA

The formula i'm using is

=SUM(COUNTIFS(INDIRECT({"n5","n7","n9","n11","n13","n15","n17"}),"<>1 MTH DUE !",INDIRECT({"n5","n7","n9","n11","n13","n15","n17"}),"<>NA"))

this essentially counts every second row and checks for the 1 MTH DUE string or the NA string and returns the value without these strings in.

this is fine for small sheets but i have other sheets holding 70+ members of staff and to use this is just an absolute pain, any ideas on how to achieve this more efficiently?

Welcome to the forum

Without seeing a representative workbook I can't be sure, but I think the following formula does what you need. It sums the odd rows between 5 and 17 and automatically ignores non-numerics.

Formula:

Please Login or Register  to view this content.

Let me know if this works for you.

I've tried the formula you suggested and it's not quite working how would i go about attaching the workbook here?

To attach a workbook, first make sure to remove any sensitive/proprietary data and then click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

Just a hunch while you're uploading the workbook. The "row()" part of the formula needs to start on an odd row, depending on how you entered it you could have ended up starting on an even row. Making the row reference absolute would be more robust.

Formula:

Please Login or Register  to view this content.

Never mind my last post - I'm summing the contents of col-N and on reading you 1st post you're counting, not summing.

I've caught up with Richard - need to see the workbook!

I think I've managed to attach the workbook, ignore everything below row 22 that's just where I was testing different methods, but as you can see the current method is manual input and a manual count within the total staff and total modules complete table. Open to suggestions, thanks.

OK, thanks.

The following formula working with your N5:N17 yields 6 which is the same as your formula in N19.

Formula:

Please Login or Register  to view this content.

Let me know if this works for you.

Appreciate the quick reply, this is almost what I'm looking for, this would work for the 'modules completed' section however I don't want it to take away from the 'total staff' when the string "1 MTH DUE !" is also in that column only if "NA" is in the column. Is there a way to still count the cells with "1 MTH DUE !" as a value using the formula you've provided?

Would it be worth using your formula for the 'Modules' completed table as it will return a count based on the dates it can read therefore it doesn't matter if it can filter the difference between the two strings I have or not.

Then perhaps using this formula to differentiate between the two strings for the total staff table?
=SUMPRODUCT(--(MOD(ROW(P4:P17)-COLUMN(P2),2)=1),--(P4:P17 <>"na"))

So just to clarify: My formula for row 19 "Modules complete" is accurate - is that correct? In the following I have adjusted the relative/absolute addressing to allow the formula to be copied across all columns. So in N19:

Formula:

Please Login or Register  to view this content.

Now for row 20 "total staff" - this will be a slightly different formula that will include (odd) rows that also contain "1 MTH DUE !". Correct? This in N20, I think will do it:

Formula:

Please Login or Register  to view this content.

Question: are the values currently in rows 19 and 20 your expected values? If so I don't understand, for example, column-T. You have T19=6 and T20=6. I calculate it to be T19=4 and T19=6. Please explain the rationale if your numbers are correct.

Hopefully we're heading in the right direction! Let me know.

Hi there, everything before column L is accurate and the rows 19 and 20 are the expected data, after column L there is data that I have fudged in the process of trying different formulae just to test out whether or not it works so ignore everything after column L.

However I have tried out both of your formulae and I do believe it's achieving what I'm after, I'm going to have a play with the other sheets using the formulae but you've really hit the nail on the head with this one!

Thanks ever so much!

Glad it's working for you. Thanks for the feedback!