Create countif where the range is defined by combining values

[Colonel]

I have a number of named ranges in tab "A" of my workbook. All ranges follow the same naming convention; XXX_BOM_GRPS, where XXX is different for each range.

In tab "B", I have a list of item numbers which start with the XXX values.

I want to create a countif function where the range is defined by selecting the first 3 digits of the item number and combining them with "_BOM_GRPS".

I've tried =COUNTIF(LEFT(item,3)&"_BOM_GRPS",item) but I get an error. Where am I going wrong?

 

[etaf]

try

=COUNTIF(INDIRECT(LEFT(item,3)&"_BOM_GRPS"),item)

see example here

I setup 2 named ranges - starting AAA and BBB

Book1
	A	B	C	D	E	F	G	H
1	A1	B1					BBB
2	A2	B2				4
3	A3	B3
4	A4	B4
5	A5
6	A6
7	A7
8	A8
9	A9
10	A10
Sheet4

Cell Formulas
Range	Formula
F2	F2	=COUNTIF(INDIRECT(LEFT(G1,3)&"_BOM_GRPS"),"B*")

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Peter_SSs]

Where am I going wrong?

The first argument of COUNTIF must be a range.

Could we have small sets of dummy data for a couple of the named ranges in "A" and items in "B" that you are trying to calculate for?
Best if you cane use XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with & to understand just what it is like and where it is. Also easy to copy from for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kvsrinivasamurthy]

May be

=COUNTIF('Tab A'!$B$2:$H$200,left('Tab B!$A2,3)&"_BOM_GRPS")

 

[Colonel]

The first argument of COUNTIF must be a range.

Could we have small sets of dummy data for a couple of the named ranges in "A" and items in "B" that you are trying to calculate for?
Best if you cane use XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with & to understand just what it is like and where it is. Also easy to copy from for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

I also suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It's the range that I'm trying to specify with the LEFT and & functions. I don't think I can use the XL2BB option (work laptop, locked down).

This shows a sample of the list of items. In the first (grey) row of column AM, I have the formula =IF([@REF]<>"",COUNTIF(GBC_BOM_GRPS,[@Item]),""). I'm explicitly setting the range name in this formula.

This shows a list of items in a named range called GDC_BOM_GRPS

Rather than explicitly stating the range name in my formula, I want to select the first 3 characters from the item number in the first screen shot and concatenate / join it with "_BOM_GRPS" to create a variable range.

 

[etaf]

i think thats what i posted earlier

 

[Colonel]

i think thats what i posted earlier

I have tried your =COUNTIF(INDIRECT(LEFT(item,3)&"_BOM_GRPS"),item) solution, but I get a #REF! error.

 

[etaf]

OK, what does item mean
using named ranges - is difficult to see here
breaking it down
then the Range that will be used is

the first 3 characters of ITEM ????
adding _BOM_GRPS"
and expecting to see that as a named range to use as the range in the countif
now its looking at that named range and trying to find the items that are in item

i have added to dropbox so you can see the named ranges

AAA_BOM_GRPS is a named range for A1:A10
BB_BOM_GRPS is a named range for B1:B10

i only keep the file on dropbox for a few days - same as the xl2bb above

Dropbox

www.dropbox.com

 

[Colonel]

My apologies etaf - your solution works. The #REF! error was my bad data.

 

[etaf]

glad to have helped
you are welcome