Countifs with HLookup

Try:

Formula:

Please Login or Register  to view this content.

Where column A contains the Study references, the "Max" header reference could be in columns A:M and the specific study to be summed is in cell N1.

If the Max header could be in a wider range, adjust $A:$M as required.

Sorry, try this for the COUNTIF

Formula:

Please Login or Register  to view this content.

What Pivot Table? Other than your brief description, I have no idea what your data/worksheet(s) look like. You said there would be a column headed "Max" ... you know where it might be, I don't.

The INDEX($A:$M,0,MATCH("Max",1:1,0)) will search for "Max" in row 1 and return an array of values from that column.

OK, I still do not know what your workbook, worksheets, data, Pivot Table(s) etc look like. I can't second guess this stuff and I'm not going to be trying to adapt a formula on that basis.

Post a sample workbook with some typical data and your expected results.

I suspect you do not have a Pivot Table.
Cell XFD1048576 is the last cell in the entire worksheet. Do you really have data in every cell

---

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!