COUNTIFS using column headers not cell ranges

[TAB_BHD]

Hoping for some help- I currently use formulas that I paste into EXCEL worksheets to create a tally based on certain criteria. The formulas reference certain column ranges to find the data (e.g., =COUNTIFS(K$2:K$361,"*"&K365&"*",G$2:G$361,"FALSE")). (Cell K365 is the location of the name we are looking to match in K2:K361 to count the associated data in G2:G361.) These formulas work fine unless IT updates the software (several times lately) that generates the EXCEL worksheet- they insert new columns, throwing off the reference locations, and the formulas subsequently stop working (until I fix them with the new column letter). Is there a way to make the formulas based on the column headers instead (which don't change), and not reference the cells below it? Maybe with a structured reference? I could change the data into a table for this, but I generally have an empty column (other than the header) and there is no space between the header and the first line of data (which I could easily insert if needed to make a table). It would be awesome if the formula could be something like: =COUNTIFS(Table1[@ExtractionBatch],"*"&K365&"*",[@RepeatTest],"FALSE") (this formula does not work- would be great if it does). (ExtractionBatch is column K and RepeatTest is column G in the previous example). Also, are there restrictions as to where in the worksheet these formulas could be pasted (e.g., below or to the right of a column they reference? Thanks so much for any help- I have spent hours trying to figure out a way...

 

[maabadi]

Welcome to MrExcel Message Board.
Why You don't use Name Manager or Define Name ( From Formula Tab). Select All Cells that you want at that column and give them Names.
Then At Formula Use that Names Not Range Address.

 

[Toadstool]

Hi TAB_BHD,

I can simulate your requirement with OFFSET and a Named range. I'm assuming the search requirement is always row 365 but the columns may change. I'm using columns C and E for my example (your G and K) and I'm using another sheet for the calculations and your COUNTIFS.

Here's the data sheet1

TAB_BHD.xlsx
	C	D	E	F
4	RepeatTest	Stuff	ExtractionBatch	Stuff
5	TRUE		Cust1
6	FALSE		Cust2
7	TRUE		Cust2
8	FALSE		Cust2
362
363
364
365			Cust2
Sheet1

Here's the Results sheet and the ranges Named with OFFSET

TAB_BHD.xlsx
	A	B	C	D
1		RepeatTest	ExtractionBatch	Customer
2	Column	3	5	5
3	Row	4	4	365
4
5	COUNTIFS=	2
Results

Cell Formulas
Range	Formula
B2:C2	B2	=SUMPRODUCT((Sheet1!$A$1:$Z$999=B$1)*COLUMN(Sheet1!$A$1:$Z$999))-COLUMN(Sheet1!$A$1:$Z$999)+1
D2	D2	=SUMPRODUCT((Sheet1!$A$1:$Z$999=C$1)*COLUMN(Sheet1!$A$1:$Z$999))-COLUMN(Sheet1!$A$1:$Z$999)+1
B3:C3	B3	=SUMPRODUCT((Sheet1!$A$1:$Z$999=B$1)*ROW(Sheet1!$A$1:$Z$999))-ROW(Sheet1!$A$1:$Z$999)+1
B5	B5	=COUNTIFS(ExtractionBatch,Customer,RepeatTest,FALSE)

Named Ranges
Name	Refers To	Cells
Customer	=OFFSET(Sheet1!$A$1,364,Results!$C$2-1,1)	B2:C3, D2
ExtractionBatch	=OFFSET(Sheet1!$A$1,Results!$C$3,Results!$C$2-1,360)	B2:C3, D2
RepeatTest	=OFFSET(Sheet1!$A$1,Results!$B$3,Results!$B$2-1,360)	B2:C3, D2

 

[Alex Blakenburg]

I am a big fan of using Tables wherever possible, structured referencing saves a lot of data range maintenance issues.
I would prefer to see you name the table something other that Table1, preferably a meaningful name and I like to have my table names sort together so I prefix them with something like db or tbl.

If you modify your table formula to the below it should work.

=COUNTIFS(Table1[ExtractionBatch],"*"&K365&"*",Table1[RepeatTest],"FALSE")

The @ symbol is used when you have a formula on the same row inside the table and indicates that the formula is referencing the current row in the named column.

Unless you don't expect your table to expand I would not put the lookup value under the table where is would get in the way of the data expanding.
Consider putting it a couple of rows above the table.

Addressing your table conversion issues:-
Space between the Header and Data
- a bit confused by this, if the header are the column headings there normally isn't a space.
- if you mean the sheet titles - then its always better to have a blank row between your column headings and titles etc. It helps Pivots, filter and tables, work out what area they should be applied to automatically. ( you can always do a manual override it it picks up unwanted adjacent rows - but its better practice not to need to do that)

Blank Columns
- table will need all columns to have a heading. If you really don't want it to appear to have one, you can always set the font colour to the same colour as the background

 

[TAB_BHD]

Thanks so much for the responses. I used the solution proposed by Alex Blakenburg (as it was the closest to what I currently use) and it worked perfectly.

 

[Alex Blakenburg]

Thank you for the update. Glad we could help.

 

[TAB_BHD]

One more slight issue- I built the formulas in one 'dummy' EXCEL file to test function (which worked fine), then copied the formulas to another EXCEL to use. The formulas now reference the columns on the original EXCEL (adding the file name before the column header even though they aren't physically in the original formula). There is obviously some invisible reference to the original file I used. Currently to get around this, I removed the "=" at the start of the formula and add it back after pasting into the new EXCEL and hit enter to activate the formulas.

Is there a simple workaround? I don't want to make a macro. Thanks again for any help!

 

[Alex Blakenburg]

Can you elaborate a little, mention of a macro indicates:-
1) you have a lot of formulas you need to bring across
(For formulas in the table itself, I would count the number of formulas based 1 formula per column ignoring the number of rows involved)

2) you are going to be repeatedly copying the formulas to another workbook

Please clarify, are either or both of the above the case ?

If you are only doing a small number of formulas, just go into the cell edit box, copy the formula as text, go to the target cells edit box and paste in the formula.

 

[TAB_BHD]

There are just four formulas total, in adjacent horizontal cells, in the same row. I 'store' them in one commonly accessible EXCEL tracking file so they are available to all users (they are not used for/in the EXCEL they are stored in). They are copied/pasted from this common EXCEL into a new EXCEL (data file) where they tabulate the reporting data we need.

In short, to make the formulas, I had opened an old data file, got the formulas to work (thanks again!) and then pasted them into the common EXCEL sheet. The first formula (in the common EXCEL) reads:

=COUNTIFS(Table1[ExtractionBatch],"*"&K365&"*",Table1[RepeatTest],"FALSE")

but when pasted into a new data file references the old data file that I created it in ('EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx') so it now magically becomes:

=COUNTIFS('EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx'!Table1[ExtractionBatch],"*"&K365&"*",'EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx'!Table1[RepeatTest],"FALSE")

How can I make it not (invisibly) link back to the original file, but only reference the new EXCEL table where it is pasted?

I have made macros in the past; tedious and I think not needed for four formulas. Thanks again for any help!

 

[Alex Blakenburg]

That sounds like quite high maintenance approach. Typically everyone uses the same uses the same template and imports their own data.
If you are continually having to push out changes to every users copy of the template, you would need a macro anyway.
Who is copying the formulas to the users spreadsheet, are they doing it or are you.
For 1 or 2 formulas I would use the edit box copy paste method, for your 4 your method is as good as any.
I tried the option of adding another column using =formulatext(cell_ref) and copying that as values to the target workbook but you then still need to remove the leading ' (single quote) from the copied formula.

Your source spreadsheet is presumably always going to have the same name, so for multiple formulas after you copy them in.

• Copy the file name part of the copied formula
  'EXCEL_FORMULA_TEST_031121-2-ED5-LP6-LP7-JE8-CV.xlsx'!
  (if you do it often you could store it somewhere)
  
  
• Hit Ctrl + H (replace)
• Paste in the path part
• Leave the "replace with" box as blank
• Hit the button Replace all
  
  
•