• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

My SUPRODUCT formula gives wrong amount

Hello,

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

Capture.PNGBook1.xlsx
0
Flora
Asked:
Flora
1 Solution
 
Phillip BurtonCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
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 BurtonCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now