sumproducts with Or

[dmheller]

All,
I have this and get #value
=SUMPRODUCT(--(INT('Line 3 data copy'!$D$4:$D$44739)=INT('Line 3 calc Sheet'!B3)),('Line 3 data copy'!E4:E44739<220)+('Line 3 data copy'!F4:F44739<220)+('Line 3 data copy'!G4:G44739<220))
D4 is a date 8/11/17 13:25
B3 is a date 8/11/17
E F and G are all data values.
I want any time one of the data vales for a given min of time so if all 3 are above 220 nothing but if one of the 3 is below, I want the sum of those for that date.
I cam close but cant get it go give me a number.
any help would be great.
thanks
heller

 

[Dave Patton]

The formula appears OK.

It looks like the formula should work.
Check column D for text; text will create a value error, The range D4:D44739 should be numbers.

 

[dmheller]

D is a date. will that matter?

 

[Aladin Akyurek]

Adjust the ranges to suit...

=SUMPRODUCT(--(INT('Line 3 data copy'!$D$4:$D$10)=INT('Line 3 calc Sheet'!B3)),--(('Line 3 data copy'!E4:E10 < 220)+('Line 3 data copy'!F4:F10 < 220)+('Line 3 data copy'!G4:G10 < 220)>0))

 

[Dave Patton]

Dates are numbers.

To avoid multiple counts, you may have to add to your formula

=SUMPRODUCT(--(INT($D$4:$D$7)=B3),--(ISNUMBER((E4:E7<220)+(F4:F7<220)+(G4:G7<220))))

I recommend that you review your formula with just a few rows. Try Excel's Formula Evaluate Formula.

I would use Aladin's suggestion.

 

[dmheller]

I got lots of multiple counts. the largest number should be 1440, min in a day and I have some that show 4320 What needs to be added?

=SUMPRODUCT(--(INT('Line 3'!$D$4:$D$44644)=INT('Line 3 calc Sheet'!B3)),('Line 3 data copy'!E4:E44644<220)+('Line 3 data copy'!F4:F44644<220)+('Line 3 data copy'!$G$4:$G$44644<220))

 

[Dave Patton]

See Aladin's suggestion or my suggestion above.

 

[dmheller]

I added that and still show multiple numbers.
=SUMPRODUCT(--(INT('Line 3'!$D$4:$D$44644)=INT('Line 3 calc Sheet'!B3)),--(ISNUMBER('Line 3 data copy'!E4:E44644<220)+('Line 3 data copy'!F4:F44644<220)+('Line 3 data copy'!$G$4:$G$44644<220)))

 

[dmheller]

We are good now. thank you so much