COUNTIFS using column headers not cell ranges

TAB_BHD

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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...
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,805
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
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
CDEF
4RepeatTestStuffExtractionBatchStuff
5TRUECust1
6FALSECust2
7TRUECust2
8FALSECust2
362
363
364
365Cust2
Sheet1


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

TAB_BHD.xlsx
ABCD
1RepeatTestExtractionBatchCustomer
2Column355
3Row44365
4
5COUNTIFS=2
Results
Cell Formulas
RangeFormula
B2:C2B2=SUMPRODUCT((Sheet1!$A$1:$Z$999=B$1)*COLUMN(Sheet1!$A$1:$Z$999))-COLUMN(Sheet1!$A$1:$Z$999)+1
D2D2=SUMPRODUCT((Sheet1!$A$1:$Z$999=C$1)*COLUMN(Sheet1!$A$1:$Z$999))-COLUMN(Sheet1!$A$1:$Z$999)+1
B3:C3B3=SUMPRODUCT((Sheet1!$A$1:$Z$999=B$1)*ROW(Sheet1!$A$1:$Z$999))-ROW(Sheet1!$A$1:$Z$999)+1
B5B5=COUNTIFS(ExtractionBatch,Customer,RepeatTest,FALSE)
Named Ranges
NameRefers ToCells
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

Well-known Member
Joined
Feb 23, 2021
Messages
575
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
=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
 
Solution

TAB_BHD

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

TAB_BHD

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 23, 2021
Messages
575
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
575
Office Version
  1. 365
Platform
  1. Windows
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

  • 1616023896372.png

 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,272
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top