match multiple criteria ina range from multiple criteria multiplet

[RG]

This is a tough one i think
been trying to use sum product but have not been able to get it to work

=SUMPRODUCT(--(Sheet1!A3:A6500=Mon!E7:E100),--(Sheet1!D36500=Mon!K7:K100),--(Mon!F7:F100>=Sheet1!E3:E6500),--(Mon!F7:F100<=Sheet1!F3:F6500),--(Sheet1!B3:B6500=Report!B7))

this is basically matching up criteria that is in one range of cells with
criteria in the other ranges of cells in multiple instances. if all five
conditions are met, it would equal 1

Sheet1 columns A,B,D,E,F
A.Range of dates, B. range of Names, D. range of random numbers, E. range
oftime in, F. range of time

Mon Columns E,F,K
E. range of dates, F. Range of dates K.range of random numbers

Report
name here needs to be found in sheet 1 in the range of names

if everything matches at least once it should equal 1. if it matches more
then obviously it would equal 2

 

[T. Valko]

Try it like this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet1!A3:A6500,Mon!E7:E100,0))),--(ISNUMBER(MATCH(Sheet1!D36500,Mon!K7:K100,0))),--(Sheet1!E3:E6500>=Mon!F7:F100),--(Sheet1!F3:F6500<=Mon!F7:F100),--(Sheet1!B3:B6500=Report!B7))

 

[RG]

tried. still getting #N/A. not sure if this will help but Not all cells
throughout the ranges have information in them.
alot of them are blank


-
RG

 

[T. Valko]

Do you have any #N/A errors in any of your ranges?

In your post you had these expressions backwards:

(Sheet1!E3:E6500>=Mon!F7:F100)
(Sheet1!F3:F6500<=Mon!F7:F100)

You had:

--(Mon!F7:F100>=Sheet1!E3:E6500)
--(Mon!F7:F100<=Sheet1!F3:F6500)

Thoses expressions will cause a #N/A error because the ranges being tested
are not the same size as the ranges in the other expressions.

 

[T. Valko]

Argh!

Disregard. Don't know what I was thinking (apparently I wasn't!).

 

[RG]

Well after you braught it up i did find a mistake
Mon!F7:F51>=Sheet1!E3:E8
Mon!F7:F51<=Sheet1!F3:F8

the referances should be like this

 

[RG]

Bah now im getting confused it ws like that already.

 

[RG]

Disregard this. i reworked a few things and figured it out