Dual Criteria

[sally100]

Hi all,

I am trying to make a formula look at a range and ignore 2 criteria but sum the rest.

i.e:

=sumif($c$3:$n$39,"<>03:45","<>07:50")

what am I doing wrong?

[olasa]

This can be done with the sumproduct formula

=SUMPRODUCT(($C$3:$N$39)*($C$3:$N$39<>TIMEVALUE("03:45"))*($C$3:$N$39<>TIMEVALUE("07:50")))

Ola Sandström

[sally100]

thanks :o)

[sally100]

sorry but this is givin me a #value response..
I have copied and pasted the formula but is not working.

[RES]

Olasa put you on the right track but you need to modify the time text string to
hh:mm:ss format
eg. "03:45:00"

Then it is fine

=SUMPRODUCT(($C$3:$N$39)*($C$3:$N$39<>TIMEVALUE("03:45:00"))*($C$3:$N$39<>TIMEVALUE("07:50:00")))
or
=SUMPRODUCT(($C$3:$N$39),--($C$3:$N$39<>TIMEVALUE("03:45:00")),--($C$3:$N$39<>TIMEVALUE("07:50:00")))

RES