Dragging vertical formulas referencing horizontal cells

[michlam]

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



Second Formula for Column A:

=TRANSPOSE(CONCATENATE(INDEX('BLSPLS Menu'!$C$12:$G$13,1,1),INDEX('BLSPLS Menu'!$C$12:$G$13,2,1)))

The formula I’m looking to create here is that I want it to concatenate rows 12-13 for each column into the cell in Summary Sold.

For example, Program abc in Summary Sold Tab has spit out the correct. Value in cell C3. However, when I drag that formula down, again it does not appear to work.

For reference, Program def should combine the text from D12:D13, but it does not.

Help would be appreciated with the formulas!!! THANK YOU!

[RaulSerg]

For the first formula try instead in F3 (you write B3 above): =COUNTIFS(INDEX('BLSPLS Menu'!$C$13:$G$14,,ROWS(F$3:F3)),"*",INDEX('BLSPLS Menu'!$C$13:$G$14,,ROWS(F$3:F3)),"<> ")

And in G3 (your write C3 above): =CONCATENATE(INDEX('BLSPLS Menu'!$C$12:$G$12,,ROWS(G$3:G3)),INDEX('BLSPLS Menu'!$C$13:$G$13,,ROWS(G$3:G3)))

[michlam]

Thanks so much! Both formulas worked

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).

Thanks so much again!

[RaulSerg]

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.

Hope this helps.

Regards

[michlam]

Thanks for the explanation & help!