My SUPRODUCT formula gives wrong amount

Hello,

can you please help me, why my sumproduct formula do not give correct amount?

Capture.PNGBook1.xlsx
LVL 6
FloraAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

pls try

=SUMPRODUCT(Expense,--(1*(LEFT(DateD,4))=G4))

I've changed the named range with minus 1 at the end
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1)

Regards
EE20150116.xlsx
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
There are two reasons:

1. You are trying to use the dates 31-38 September 2013.
2. You are also using "2013", which is not a date.
0
 
Leon KammerCommented:
The data type you have in the left hand column must be constant, so if you have 2013 it will take this as an integer, not a date.
Also you have dates that are not valid.
If you put in 2013-01-01 for 2013 and 2014-01-01 for 2014, this will bring the SUM back into line.

Cheers

Leon
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
FloraAuthor Commented:
my data is huge and if i keep changing every one of the cells it will take forever.

how the sumproduct formula can be adjusted, so that it considers that 2013 and 2014 as well. without changing anything in the source data. ?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change the formula to

=SUMPRODUCT(Expense,--ISNUMBER(MATCH(YEAR(DateD),G4,0)))+SUMIF(DateD,G4,Expense)

However, that will not total the information for 31-38 September, so that if that is genuine data, then you should correct the data in that instance, not the formula.
0
 
Pratima PharandeCommented:
Copy-of-Book1.xlsx

check this
0
 
FloraAuthor Commented:
Thank you all.

only Rgonzo1971  solutions worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.