Row to Columns with COUNTIF

[DSwartz]

I have a function set up on SheetA to count the number of cells with "X" in them on a SheetB: COUNTIF(SheetB!$B$3:$B$50,"X"). I have several additional training types listed on SheetA which are identical to row B on SheetB, but they are layed out as a column on SheetA. In other words, B2:AJ2 on SheetB is the same as A2:A36 on SheetA. I want to be able to drag down my COUNTIF formula on SheetA and have it count the relevant columns to the right in SheetB. I've tried various combinations of INDEX and MATCH which I think is the key here, but I can't seem to make it work within an array. Any thoughts on how I can accomplish this? Thanks in advance for any assistance.

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[FDibbins]

It might be easier to offer suggestions if we could see a sample (workbook) of what you are working with?

[martindwilson]

=COUNTIF(INDEX(sheetb!$B$3:$AJ$50,,ROWS($A$1:A1)),"x") filled down will reference columns b through aj

[DSwartz]

martindwilson, that formula nailed it! Thanks for the help! I was very close to what you came up with, but didn't realize I could use the double comma to leave out the row argument like you did so that the ROWS function provides the column number. Very crafty!

[Tony Valko]

Omitting the ROW argument causes it to be evaluated as 0 which means "all rows".