Sumproduct returns #VALUE! when summing up values

[Eddyke]

Hi,

I'm having a small problem when summing up values in column G.

In column A, there is a list of dates. Depending on the value of J2 (where is mentioned the year) it should sum all values in column G that are related to that year.

However, sometimes there is no year known and then the value of the date in column A is blank (""). Those values should not be summed.

I've tried the following formula:

SUMPRODUCT(--(YEAR($A$2:$A$1000)=$J2);$G$2:$G$1000) ==> returns #VALUE!

So, I've tried to check if the values are blank with the following formulas:

SUMPRODUCT(--(YEAR(ISNUMBER($A$2:$A$1000))=$J2);$G$2:$G$1000) ==> returns 0

or SUMPRODUCT(--(ISNUMBER($A$2:$A$1000));--(YEAR($A$2:$A$1000)=$J2);$G$2:$G$1000) ==> returns #VALUE!

So, can please one of your experts help me in this matter?

Thanks a lot and have a nice day.

Best regards


So

 

[Georgiboy]

Would it not work better with Sumif:

=SUMIF($A$2:$A$1000,$J$2,$G$2:$G$1000)

 

[Snakehips]

@Eddyke I suspect that one or more of the date cells that you assume to be blank actually contain a hidden character?
Eg a space.
Otherwise, there should be nothing wrong with your initial formula.

 

[Eddyke]

@Eddyke I suspect that one or more of the date cells that you assume to be blank actually contain a hidden character?
Eg a space.
Otherwise, there should be nothing wrong with your initial formula.

Some date cells contains "". As a matter of fact, it is a formula which refers to another cell. Depending on a certain condition, it takes the date or a blank ""

Would it not work better with Sumif:

=SUMIF($A$2:$A$1000,$J$2,$G$2:$G$1000)

I've also tried that but unfortunately, in column A is a full date (e.g. 01/02/2022). Cell J2 contains only the year 2022. So if I try the formula SUMIF(year($A$2:$A$1000),$J$2,$G$2:$G$1000) then it seems that there is something wrong with this formula.

 

[Snakehips]

Then it will be the YEAR function that is creating the error.

Try

=SUMPRODUCT(--(IFERROR(YEAR($A$2:$A$1000),0)=$J2),$G$2:$G$1000)

 

[Eddyke]

=SUMPRODUCT(--(IFERROR(YEAR($A$2:$A$1000),0)=$J2),$G$2:$G$1000)

This gives 0. But isn't that caused because in some cells there is not a date sothat the formula YEAR() can not work?