SumIF generates a #VALUE!

[ClausSkeel]

Hi all.

I got a sum if function, that looks like this:

=SUMIF('(PATH)'!$F:$F;">"&(EOMONTH(TODAY(); -1)+1);'(PATH)'!$H:$H)/1000

But it generates an error, because of the closed workbook.
Can somebody help me create a SUMPRODUCT or SUM(IF - function?

 

[Alex Blakenburg]

See if this works for you.

=SUM(IF('(Path)'!$F:$F>(EOMONTH(TODAY(),-1)+1);'(Path)'!$H:$H/1000;0))

PS: Based on your formula are you sure you don't want >= (instead of >) ?
Excluding the 1st of the month seems unlikely.

 

[ClausSkeel]

See if this works for you.

=SUM(IF('(Path)'!$F:$F>(EOMONTH(TODAY(),-1)+1);'(Path)'!$H:$H/1000;0))

PS: Based on your formula are you sure you don't want >= (instead of >) ?
Excluding the 1st of the month seems unlikely.

Sure of course Alex, sorry

 

[ClausSkeel]

Sure of course Alex, sorry

It still generates a #VALUE! error, why is that?

 

[Alex Blakenburg]

Value is an issue with the data not the formula.
Do you have any text in column H of the closed workbook ?

 

[ClausSkeel]

No text, but quantities

 

[ClausSkeel]

F:F are dates and H:H are quantities in kgs.

 

[Alex Blakenburg]

Since you are using whole column referencing I imagine you have a Text heading it's picking up.
Try this

=SUM(IF(('(Path)'!$F:$F>(EOMONTH(TODAY();-1)+1))*ISNUMBER('(Path)'!$H:$H);'(Path)'!$H:$H/1000;0))

 

[ClausSkeel]

Since you are using whole column referencing I imagine you have a Text heading it's picking up.
Try this

=SUM(IF(('(Path)'!$F:$F>(EOMONTH(TODAY();-1)+1))*ISNUMBER('(Path)'!$H:$H);'(Path)'!$H:$H/1000;0))

It works perfect! - many thanks :D

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.