Hi there, I’m looking to get help with 2 formulas. The issue with both is that I want to drag down the formula VERTICALLY, but the reference cells are horizontal.
Both formulas are for the “Summary Sold” tab.
First formula for Column B:
=COUNTIFS('BLSPLS Menu'!C12:C13,”*”,’BLSPLS Menu'!C12:C13,”<> ")
The formula I’m looking to create here is that I want it to count the number of cells from C12:C13 in BLSPLS Menu tab that have text in it.
Cell B3 in Summary Sold returns the correct value 2. HOWEVER, the issue I’m having down is dragging down the formula to the rest of the column, as the C12:C13 value is shifting incorrectly.
I’ve tried adding in $ to lock the values, but that doesn’t seem to help… any ideas on what other formulas I’ll need to do and how I can integrate it? I’ve read online something about INDEX?? But not sure if that’s applicable here
Just some general questions if you wouldn't mind explaining
- what are the two commas for?
- Are you able to elaborate a bit further on the Index formula you used, and specifically what the addition of the ROWS does? I personally have never used it before (am still new to excel).
If you omit the row or column arguments for INDEX, or set it to zero, it returns the entire row/column.
I prefer to just omit it (instead of zero) as it saves me one character.
The first comma is for the row argument, the second comma then is for the column argument; and since you want to move to the right, you enter the counter (ROWS()) into the column argument and don't need anything for the row argument.
ROWS is just a counter, in F3 this is 1, in F4 this is 2 etc.
I use the counter in the same cell that the formulas is in, i.e. the reference for the counter in F3 refers to F3 for two reasons:
- it's kinda fancy, and at first a bit counterintuitive, that a formula that refers to its own cell does not create a circularity problem
- it avoids from the formula being corrupted; e.g. if you use e.g. cell A1 for the counter and then delete cell A1, the formula in F3 will no longer work (showing a #REF! error where the reference to A1 was); referring to its own cell instead, avoids this from happening.