COUNTIF?

[Bill Oertell]

I sure hope someone can help. I need a function like COUNTIF except that I want
it to find the range to count based on the row and column headings it's used in
rather than having to state it explicitly. The row would be the sheet to look
at and the column heading would be the column to match in that sheet. So, if
the row heading was "3456" and the column heading was "ABC", then the function
would look at sheet "3456" and count the number of matching things in the column
with the heading "ABC". This would be used in a sheet separate from the sheets
the function needs to look at but part of the same workbook. And to throw a
wrench in the whole mess, the column headings may not be in row 1. The column
headings might be in some other row...say 5 or 6.
Any help would be greatly appreciated. Many thanks!

 

[Arvi Laanemets]

Hi

Something like (on fly)
D10=COUNT(INDIRECT("'"&$A10&"!'"&D$5&":"&D$5))

 

[Bill Oertell]

Nope. Didn't work. I got it to refer to the page but not the column.

 

[Arvi Laanemets]

Hi

I read your original message over, and I see I missed that you don't refer
to column (A or AB or...) but for column header. It complicates the matter a
bit.

Try this (with sheet name stored in A1, the column heading in A2 and row
number of header in A3 on sheet with SUMIF formula)
=COUNTIF(OFFSET(INDIRECT("'"&$A$1&"'!$A$1");$A$3-1,MATCH($A$2,INDIRECT("'"&$
A$1&"'!"&$A$3&":"&$A$3),0)-1,65536-$A$3,1),">0")

 

[Bill Oertell]

I'll give it a try and let you know how it comes out.