SUMPRODUCT with an IF Statement

[Gymnast14]

I have learned how to use the SUMPRODUCT formula, thanks to the lovely FAQ section. However, I am trying to add one more step to my formula.

I want to say:

If ANY of the cells in the range H1:H10 are greater than or equal to 01/31/2007, then move on to the SUMPRODUCT formula. If not, 0.

I'm stuck because it's only moving on to the SUMPRODUCT if all of the values in range H1:H10 are = 01/31/2007.

How can I do this?

Here is what I have currently:

'=IF(($H$1:$H$10=DATEVALUE("01/31/2007")),SUMPRODUCT(($G$1:$G$10<="SAMPLE")*($Q$1:$Q$10="RED")),0)

 

[AnotherHiggins]

For the IF part, use this syntax:

=IF(COUNTIF(H1:H10, ">=" & DATEVALUE("1/31/07"))>0,True,False)

Or you can use a cell reference for the date you want to check against. Put 1/31/07 in, say, A1, then use this:
=IF(COUNTIF(H1:H10, ">=" & A1)>0,True,False)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

howsabout

=IF(MAX(H1:H10)>DATEVALUE("1/31/07"),SUMPRODUCT Formula,0)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question