SUMPRODUCT based on YEAR, DATE or DATEVALUE

[pr1m]

This is probably going to be an easy one.

I have read through several postings and tried to build a formula using SUMPRODUCT but its not working as expected. I am trying to sumproduct 2 columns based on the date value in a third column. I've tried using an embedded DATE, DATEVALUE and YEAR function but nothing seems to work right. I've attached an example of what I've tried so far. I'm trying to multiply the "Unit" column by the "Cost" column if the "Date" column value is from the year 2018. The output is not even close and i cant seem to get it to work right.

Thanks in advance for all the help!

[AliGW]

Welcome to the forum.

=SUMPRODUCT((YEAR(A2:A8)=2018*C2:C8<>"")*D2:D8)

[pr1m]

Hi Ali...
Just double checking the formula. I dont have anything in columns D, so I am assuming I would swap C for B and D for C, correct? Also, can you explain, briefly and hopefully plainly where I went wrong?

Thanks!

[AliGW]

The formula I have given you is based on the attachment - I don’t know how that compares to your real workbook.

For both criteria to be met, you need the AND operator *.

[pr1m]

Ok, I'm not sure what happened when I attached the workbook but there it nothing in column D. So, I simply adjusted your formula to use columns A, B and C. That done the output is still wrong but there is progress. The output of the formula = 987.74, instead of the expected 51927.37 Based on the desired variable conditions being met, column A's year value is 2018 the following should be done: [(b2*c2)+(b3*c3)+(b4*c4)] equating to the expected $51K. What do you reckon is missing?

[oeldere]

D2 = year(a2)
E2 = month(a2)
F2 = B2*C2

After that a pivot table.

See the attached file.

[protonLeah]

Please Login or Register  to view this content.

note:
=SUMPRODUCT(B2:B8,C2:C8,(YEAR(A2:A8=2018)))
YEAR(A2:A8=2018) <>YEAR(A2:A8)=2018

[AliGW]

It gave the 51000 result in your sample workbook for me.

[pr1m]

Hi oeldere! Thanks for the response!

The sample I provided is part of a much larger spreadsheet. So I'd like to avoid pivot tables and a fixed variable location if possible. Instead finding a working formula would be much better as I can update the formula easier than the entire worksheet.

[pr1m]

Hi protonLeah! Thanks for the response!!

The formula you provided worked!!! Now to make sure I understand how it works: you are using a boolean argument on column A and if that output is true the corresponding column b is multiplied to column c and then summed. That correct? If not, where do I have it wrong?

Thanks again!

[oeldere]

@PR1M

In that case you don't know the power of pivot table.

It is easy to calculate new info, by just drag and drop.

Of course, I don't mind that you use sumproduct. I used it a lot before I knew pivot table.

After I know pivot table, I use Pivot Table a lot (instead of sumproduct).

[pr1m]

oeldere

I'm very aware of the power of pivot tables. I use them a lot in other scenarios. Its just in this specific case it wouldn't work right.

[protonLeah]

"... you are using a boolean argument on column A and if that output is true the corresponding column b is multiplied to column c and then summed. That correct? ..." Yes