suggestions?: sumproduct question

[HBF]

My spreadsheet tracks employee response to being called out from home to work after normal hours.

On the first page (sheet name Y-T-D) is a list of names where I would like to show a count of the number of times the employee responded.

Cells A6-A9 contain the employee names. Cells I5 - L5 contain the responses.


There are also 12 worksheets (Jan thru Dec) which track how many times employees were called that month and their response.

The names on the monthly sheets are listed in column D. The responses on the monthly sheets are listed in column G.

In cell I6, I tried: =SUMPRODUCT(('Jan:Dec'!D:D=A6)*('Jan:Dec'!G:G=I5)) and the result is #NUM!

I also tried: =SUMIF(Jan:Dec!D:D,'Y-T-D'!A6,Jan:Dec!G:G) and the result is
#VALUE!

Any suggestions?

I would attach the sheet, but I believe it is too large - compressed size is 29mb.

[JeanRage]

In cell I6, I tried: =SUMPRODUCT(('Jan:Dec'!D:D=A6)*('Jan:Dec'!G:G=I5)) and the result is #NUM!

Hi,

When using the sumproduct function, you need to specify your ranges...

=SUMPRODUCT(('Jan:Dec'!D2:D1000=A6)*('Jan:Dec'!G2:G1000=I5))

HTH

[DonkeyOte]

As inferred, pre XL2007 neither SUMPRODUCT nor Array can use entire column references - nor should you want to - both routes are expensive and it's imperative ranges are kept as lean as possible.

Re: your question... you can not conduct conditional calcs in 3D without using expensive volatile formulae. I would suggest you consider using helper cells on each sheet.

John McGimpsey provides an overview of the various routes: http://www.mcgimpsey.com/excel/threedsumif.html

[HBF]

I used the sumproduct formula in each monthly worksheet then the sumif formula on the summary sheet.

Thanks to all!