Need help understanding how to combine Count and Indirect

Im working on a solution right now and I'm trying to combine Count and Indirect but I don't understand what the [a1] section is supposed to be

=count(indirect(c4:c10, [a1]))

C4:C10 is the range of possible types that I want to count and Im guessing [a1] has to be one of the types from the range so that it can produce a count but it wont work.

Am I misunderstanding the function?

Thanks,

-A

Are you confusing it with COUNTIF?

Pete

Originally Posted by Pete_UK

Are you confusing it with COUNTIF?

Pete

thanks for the reply Pete.

Isn't the [a1] part from the indirect function? Im trying to count the number of identical cells from a column then put it in a table. I'm using the indirect function so that I can drag the values across.

I think the formula should be =count(indirect(C4:C10 but I don't get how Im supposed to count the value of the text.

So for the column C4:C10 the values are Product A, Product B, Product C and Product D but how do I add that to the formula so that I can count the number of times they are used?

Thanks

-A

Originally Posted by FDibbins

I think Pete is right, try using =countif()

=countif($c$4:$c$10,a1)

indirect() is used when you want to reference a cell containing text (or a number) for use in a formula. So, for instance, you could use indirect() to include a sheet name that you might have on sheet1, that you want to use to pull in data from different sheets.

If you have cell B1 with the text sheet2 in it, and call A1 =INDIRECT(B1&"!A1") every time you change the value in B1 to a different sheet name, it will return the value of that cell in that respective sheet

Thanks FDibbins that does make sense but what if the situation is like the attached. Shouldn't I use indirect then? The document that Im using is really large so I've shortened it down. The red text are the numbers that I have been trying to populate. Since the values I'm searching are text like "1.0.A.3" then I thought the indirect made sense along with the count but Im not sure how to get it so that I can insert a function in F4 and then drag it to H5.

Let me know if that doesn't make sense. Appreciate everyone's help!

Thanks,

-AExampleofQ.xlsx

Originally Posted by FDibbins

see if the attached is what you want? I had to include a helper column to populate the divisions (it was the simplest way), you can hide this if required.

That should work. Appreciate the help FDibbins and Pete!