Conditional Sum between worksheets or better way to do this?

[mjcdriscoll]

I have to do a break down of evaluations. I have a spreadsheet that has employee names, supervisor, manager, location & then roughly 30 questions as my headers. The questions are all answered with "yes, no or na". I need to get a breakdown of by employee name, supervisor, manager & location the number of each answer by question. I have the data on sheet 2 in my workbook & am attempting to put a summary on page 1.

I tried this calculation (=IF(Sheet2!$B:$B=Sheet1!$A3,COUNTIF(Sheet2!$P:$P,"yes"),""))but it returns a blank.

I then thought it was supposed to be an array so I tried {=IF(Sheet2!$B:$B=Sheet1!$A3,COUNTIF(Sheet2!$P:$P,"yes"),"")}. This then reutrned an error of #NUM!.

Should I be using a different calculation or something

TIA for the help!

Thanks,

Mark

Excel 2003 SP#

[JBeaucaire]

Hi MJCDriscoll.

Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and put a clearer title on this thread. Perhaps, "Conditional Sum between worksheets"

While you're in there, click GO ADVANCED and use the paperclip icon to post up your workbook. We can help you better if we see what you're actually trying to do.

[DonkeyOte]

Generally speaking preXL2007 you are looking at SUMPRODUCT here, eg:

Please Login or Register  to view this content.

However this will prove expensive in performance terms if you have lots of data and you're generally speaking better off reverting to a Pivot Table based approach...

That said given your present layout of yes/no/na options and 59 question columns to surmise this is not going to work very well either... you would perhaps be better off revising your underlying table such that it works along the lines of a standard data table (that is to say Q becomes a field and you have one row of data per response).

In the attached I have gone through the process of reverting your initial data table into a more traditional layout via a multi consolidation PT process, the revised layout will make subsequent analysis far easier and more efficient and can be used as basis for a more traditional Pivot Table with ease.

This one-off task of converting the original data into the revised layout via transposition etc may seem long winded but I would say that if you're doing a decent amount of analysis it's likely to prove beneficial long-term.