Count If Non-Contiguous Formula - How can I drag this down?

Hi All,

I have the following COUNTIF formula which serves to count only those cells where the formula (in aforementioned cell) returns a value i.e. this formula ignores those cells where the formula returns no value (as you know COUNTA is not suitable to count non-blank cells with formulas as it always counts cells with formulae in).

=SUM(COUNTIF(INDIRECT({"I7","L7","O7","R7","U7"}),">"""))

I need this formula to be active on over 2,000 rows. The first row I need it in is row 7 (hence the cell references above) and then I attempted a simple drag down exercise for the next 2,000 rows however, after dragging the formula down it became clear to me that the row references in this formula (I7, L7, O7, R7 & U7) were not changing with the rows. Is there any quick way to overcome this problem as I don't fancy changing 2,000 formulae manually.

Thanks in advance

So to give you more detail:

- Columns G, H & I: G and H contain non numeric data e.g "C2" & "B1". Column I contains a concatenate formula e.g. "C2 B1"
- Columns J, K & L: J and K contain non numeric data e.g C2. Column L contains a concatenate formula e.g. "C2 B1"
- Columns M, N & O: M and N contain non numeric data e.g C2. Column O contains a concatenate formula e.g. "C2 B1"
- Columns P, Q & R: P and Q contain non numeric data e.g C2. Column R contains a concatenate formula e.g. "C2 B1"
- Columns S, T & U: S and T contain non numeric data e.g C2. Column U contains a concatenate formula e.g. "C2 B1"

Column G & H and so on are primary and secondary stock code locations e.g. C1 = Container 1 and B2 = Shelf 2. Column I and so on is the full stock location e.g. Shelf 2 in Container 1.
The five groups (Group 1 = G, H and I, Group 2 = J, K & L etc. etc.) represent a stock location. So on the basis that one row represents one unique stock item, I will first use Group 1 to record the first stock location. If there are so many units that the stock item must be contained in two locations (two shelves) then Group 2 will be used to show the second location and so on and so forth.

COUNT doesn't work because the data is non-numerical.

Tony Valko, your formula doesn't work presumably for a similar reason, although using your formula returns a result of 5 regardless of whether there is any value returned in the concatenate cells or not, whereas FDibbins formula returns a value of zero (again regardless of whether there is a value in the concatenate cells or not).

Tony's formula works as far as I understand your requirement.

E.G. With "C2 B1" in various cells (I, L, O,R,U) I get the correct count for each row.

Tony's formula works as far as I understand your requirement.

E.G. With "C2 B1" in various cells (I, L, O,R,U) I get the correct count for each row.

Hi All,

Not been able to load this website for a couple of hours. I wanted to edit my post to contain the below paragraph (see below bold comment) but since there have been more replies. I've also attached a simple version of my worksheet. Can I take liberties by drawing your attention to the macro query too? (Thanks!!!!)

My formula is in Column V and aims to count the number of locations that each item is stored in. The reason I need this information is because I eventually want to create a macro that, on a separate tab (that also contains the 2,000 lines of stock items) will generate x number of lines below each row that the stock item is contained in, with 'x' being the number in Column V. If anyone can help with this, that would be even better...one can dream...


FYI the attached contains 4 tabs:
1 - 'CurrentIssues' shows the formula suggested by Tony and FDibbins doesn't work (seen some comments that say it does so wanted to demonstrate it doesn't on mine)
2 - 'StockLocation - HowItShouldWork' demonstrates the values that Column V should display
3 - 'StockCount' is a spreadsheet I want to use to conduct stock counts (this is the macro part). Basically I need a list to use for my monthly stock counts where I can input the unit numbers at each location. But as our stock stores are very fluid and items can be moved from one location to another on a quarterly basis, I require this sheet to be linked to the 'StockLocation' sheet
4 - 'StockCountExample1' just shows you how I would use the 'StockCount' list to conduct a stock take.

Try

=COUNTIFS($G$6:$U$6,"Full Location",G7:U7,"<> ")


The problem you've been having is your 'Blanks' are not really blank (even by formula blank standards)
They contain a space, resulting from this formula
=G7&" "&H7

Thanks guys.

Everyone on here amazes me. I've worked with Excel at a basic level for years but been learning like crazy this past year to really advance my skills. And just when I thought I'd got to a competent level, I look at how quickly you all solve solve problems and it just astounds me. Such an obscure problem in my formula and you pick it up so quickly.

Both solutions work perfectly. Does anyone know how to create that Macro I referred to?

You're welcome.