Need to change a Sumproduct Formula to incorporate a condition

[john dalton]

I have have sumproduct formula that works well, however I need a section of it to use a condition.

"=SUMPRODUCT(--(('Input Sheet'!$A$10:$A$20009=3)+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1)=3),--('Input Sheet'!$L$10:$L$20009>=G21),--('Input Sheet'!$L$10:$L$20009<=J21))"

The first section ('Input Sheet'!$A$10:$A$20009=3) looks at column of data and checks if any of the data says 3, however I need the formula to look at the column of data and determine if the figure is =>3 and also =<5.

Hope this is possible.

Thanks in advance

JD

[:) Sixthsense :)]

Try this...

=SUMPRODUCT(('Input Sheet'!$L$10:$L$20009>=3)*('Input Sheet'!$L$10:$L$20009<=5),('Input Sheet'!$A$10:$A$20009=3)+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1))

[john dalton]

How do I incorporate the whole thing including the end part of the original sumproduct as in the original post?

Cheers
JD

[john dalton]

The =>3 and =<5 are direct to column A in the input sheet not column L as you have posted.

Cheers

JD

[Izandol]

=SUMPRODUCT(--((('Input Sheet'!$A$10:$A$20009>=3)*('Input Sheet'!$A$10:$A$20009<=5))+('Input Sheet'!$B$10:$B$20009=1)+('Input Sheet'!$C$10:$C$20009=1)=3),--('Input Sheet'!$L$10:$L$20009>=G21),--('Input Sheet'!$L$10:$L$20009<=J21))

With 2010 you may also use:
=COUNTIFS('Input Sheet'!$A$10:$A$20009,">=3",'Input Sheet'!$A$10:$A$20009,"<=5",'Input Sheet'!$B$10:$B$20009,1,'Input Sheet'!$C$10:$C$20009,1,'Input Sheet'!$L$10:$L$20009,">="&G21,'Input Sheet'!$L$10:$L$20009,"<="&J21)

[john dalton]

Many thanks great result.

Cheers

JD