Sumproduct & multiple criteria - one answer

[Tony]

I am trying to use sumproduct to scan a worksheet for two criiteria that
match and supply one value that matches the data type row (i.e. Total) under
the appropriate date column (i.e. 12/04/07). I am using the following
statement "

=SUMPRODUCT(('Daily Entry Logs'!C11='2007 Daily Sheet'!C4)*('Daily Entry
Logs'!B2:B48='2007 Daily Sheet'!B9)*('Daily Entry Logs'!C17:W48))

Source Sheet
Daily Entry Logs

Cell A Cell B Cell
C Cell D
Row 1
12/03/07 12/04/07
Row 2 FLAG POSTINGS Total 1 2
Row 3 Misallocations 2
3
Row 4 Other Work Stack 3
4


Target Sheet
2007 Daily Sheet

Cell A Cell B
Cell C
Row 4 DATE December
4, 2007


Row 8 FLAG POSTINGS (FP'S, AF'S)
Row 9 Total
#N/A (should be 2)
Row 10 Misallocations
#N/A (should be 3)
Row 11 Other Work Stack #N/A
(should be 4)

AS you can see I get the #N/A error, I have data in all of the source cells
the minimum being zero (0). It seems that if I reference the date cells as a
range ift doesn't work and if I reference the source data cells it also
doesn't work.

Canyone help out?

 

[Ragdyer]

I haven't really examined your scenario, *BUT* ... what I see ... right off
the bat, is that the range sizes in your formula are *not* equal!

B2:B48 versus C17:W48

This can cause a #N/A error.

 

[daddylonglegs]

Looks like you'd be better off using INDEX/MATCH, i.e. something like

=INDEX('Daily Entry Logs'!C2:W48,MATCH('2007 Daily Sheet'!B9,'Daily Entry
Logs'!B2:B48,0),MATCH('2007 Daily Sheet'!C4,'Daily Entry
Logs'!C1:W1,0))