sumif

[dmheller]

All,
I really hope this is the last time for this but I have a sum if function that is not returning any value.
{=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$750),IF(INT('Line 5 data copy'!$N$5:$N$750)=INT('Line 5 calc Sheet'!B3),--(('Line 5 data copy'!$O$5:$O$750<400)*('Line 5 data copy'!$P$5:$P$750<400)*('Line 5 data copy'!$Q$5:$Q$750<400)*('Line 5 data copy'!$R$5:$R$750<400))<0)))}
so column N is a date looking up times for the day in B3
O, P, Q, and R are numbers.
This is an and function, all have to be below 400.
I know this happens in the data but I always return 0 for every date. Any help would be great.
thanks

 

[Chrisdontm]

This is just one observation;
The way you are laying it out looks like you should be using a SUM function around each of those ranges,
or perhaps it needs to be entered as an array formula, (Ctrl + Sht. + Entr )

 

[Marcelo Branco]

All,
I really hope this is the last time for this but I have a sum if function that is not returning any value.
{=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$750),IF(INT('Line 5 data copy'!$N$5:$N$750)=INT('Line 5 calc Sheet'!B3),--(('Line 5 data copy'!$O$5:$O$750<400)*('Line 5 data copy'!$P$5:$P$750<400)*('Line 5 data copy'!$Q$5:$Q$750<400)*('Line 5 data copy'!$R$5:$R$750<400))<0)))}
so column N is a date looking up times for the day in B3
O, P, Q, and R are numbers.
This is an and function, all have to be below 400.
I know this happens in the data but I always return 0 for every date. Any help would be great.
thanks

I think the formula should be
=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$750),IF(INT('Line 5 data copy'!$N$5:$N$750)=INT('Line 5 calc Sheet'!B3),IF(('Line 5 data copy'!$O$5:$O$750<400)*('Line 5 data copy'!$P$5:$P$750<400)*('Line 5 data copy'!$Q$5:$Q$750<400)*('Line 5 data copy'!$R$5:$R$750<400),1))))
Ctrl+Shift+Enter

Try it

M.

 

[Marcelo Branco]

Or

=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$10),IF(INT('Line 5 data copy'!$N$5:$N$10)=INT('Line 5 calc Sheet'!B3),--(('Line 5 data copy'!$O$5:$O$10<400)*('Line 5 data copy'!$P$5:$P$10<400)*('Line 5 data copy'!$Q$5:$Q$10<400)*('Line 5 data copy'!$R$5:$R$10<400)>0))))
Ctrl+Shift+Enter

M.

 

[dmheller]

I tried both those equations you gave me and got nothing to return again.
Let me try it with dummy data and see if it returns then.

 

[dmheller]

Thank you, I guess when I edited my function down on the dates, it kept going back to B3 for the date and not moving down. All is well. thanks

 

[Marcelo Branco]

I tried both those equations you gave me and got nothing to return again.
Let me try it with dummy data and see if it returns then.

With my small test data both formulas worked for me.

Sheet Line 5 data copy

	N​	O​	P​	Q​	R​
4​	Date​	value1​	value2​	value3​	value4​
5​	27/09/2017​	500​	100​	500​	100​
6​	27/09/2017​	500​	100​	100​	100​
7​	27/09/2017​	300​	100​	100​	100​
8​	27/09/2017​	500​	100​	100​	100​
9​	27/09/2017​	200​	100​	300​	100​

<tbody>
</tbody>

Sheet Line 5 calc Sheet

	B​	C​	D​
1​
2​	Ref Date​	Formula1​	Formula2​
3​	27/09/2017​	2​	2​
4​

<tbody>
</tbody>

Array formula in C3 (post 3)
=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$750),IF(INT('Line 5 data copy'!$N$5:$N$750)=INT('Line 5 calc Sheet'!B3),IF(('Line 5 data copy'!$O$5:$O$750<400)*('Line 5 data copy'!$P$5:$P$750<400)*('Line 5 data copy'!$Q$5:$Q$750<400)*('Line 5 data copy'!$R$5:$R$750<400),1))))
Ctrl+Shift+Enter

Array formula in D3 (post 4)
=SUM(IF(ISNUMBER('Line 5 data copy'!$N$5:$N$750),IF(INT('Line 5 data copy'!$N$5:$N$750)=INT('Line 5 calc Sheet'!B3),--(('Line 5 data copy'!$O$5:$O$750<400)*('Line 5 data copy'!$P$5:$P$750<400)*('Line 5 data copy'!$Q$5:$Q$750<400)*('Line 5 data copy'!$R$5:$R$750<400)>0))))
Ctrl+Shift+Enter

Check if the dates in column N are real dates (numbers), not text. Check also B3 in the second sheet.

M.

 

[Marcelo Branco]

Thank you, I guess when I edited my function down on the dates, it kept going back to B3 for the date and not moving down. All is well. thanks

You are welcome. Thanks for the feedback.

M.