# Sumproduct formula won't work after copied

#### FrigidDigit

##### Board Regular
Hi all,

I have this formula below to find the latest date for an invoice by company name and it worked perfectly in the 1st cell it was created in. But when I copied it across columns, it doesn't work.

Can anyone tell me what stupid mistake I made this time?

Thanks!

=SUMPRODUCT(--('Invoice Listing'!\$J\$2:\$J\$1000=H\$2),--('Invoice Listing'!\$E\$2:\$E\$1000=MAX('Invoice Listing'!\$E\$2:\$E\$1000))*'Invoice Listing'!\$E\$2:\$E\$1000)

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Are you in the Invoice Listing Sheet? You may be creating a circular reference, which means your are refering to the cell or cells that you are creating/copying the formula in.

If so, you will have to move the formulas out of the range that the formula refers to (i.e below row 1000)

HI NBVC

Hi Thanks for the response!

Nope, I am in a summary sheet.

Any other ideas?

FD

Do you get an error message pop-up? If so, what message.

or, do you get #ref!, #value!, #na! results in the cells?

No error message it simply returns a zero value although I have manually calculated that there should be a date.

If I change the formula (below)that was copied to the next column to refer to B\$2 instead of H\$2 it returns the correct date for the company name in B2. However while H2 also contains a company name, it fails to match to it. I have confimed that the company name in H2 and in the list is identical.

=SUMPRODUCT(--('Invoice Listing'!\$J\$2:\$J\$1000=H\$2),--('Invoice Listing'!\$E\$2:\$E\$1000=MAX('Invoice Listing'!\$E\$2:\$E\$1000))*'Invoice Listing'!\$E\$2:\$E\$1000)

Thanks!
FD

...and you are sure that there is a corresponding "H2" match in the J column, where the maximum value in E within the same row of the E range?

Just to be sure that I understand correctly:

Yes the company name in H2 appears in the J column.

And I want to find the latest date for which this company name appears.

Thanks for being so patient, I realize I'm not explaining things so clearly.

FD

FrigidDigit said:
No error message it simply returns a zero value although I have manually calculated that there should be a date.

If I change the formula (below)that was copied to the next column to refer to B\$2 instead of H\$2 it returns the correct date for the company name in B2. However while H2 also contains a company name, it fails to match to it. I have confimed that the company name in H2 and in the list is identical.

=SUMPRODUCT(--('Invoice Listing'!\$J\$2:\$J\$1000=H\$2),--('Invoice Listing'!\$E\$2:\$E\$1000=MAX('Invoice Listing'!\$E\$2:\$E\$1000))*'Invoice Listing'!\$E\$2:\$E\$1000)

Thanks!
FD

You should be using something other than SUMPRODUCT to return a value. Post some sample data and expected results.

FrigidDigit said:
Just to be sure that I understand correctly:

Yes the company name in H2 appears in the J column.

And I want to find the latest date for which this company name appears.

Thanks for being so patient, I realize I'm not explaining things so clearly.

FD

What I was asking is, if in column E there the maximum number appears beside any of the company names where H2 is matched, because your formula is looking for the company name only when the maximum value in column E appears in the corresponding E-row where the H2 company appears. ....

Maybe, like Brian suggests, it's probably better to post an example and tell us exactly what you want to do and what results you expect....

Thanks Brian

I have a list of data in the format (simplified):
Subname Date

Then I have a summary sheet in the format:
Subname
Last Period billed: Date

So I want to lookup the last date that the subname billed us for.

If could tell me how to paste an image of the spreadsheet I'd be happy to do that if it would help.

FD

Replies
1
Views
128
Replies
2
Views
224
Replies
9
Views
248
Replies
1
Views
96
Replies
5
Views
456

### Forum statistics

1,221,053
Messages
6,157,640
Members
451,426
Latest member
VinnyDoesntKnowExcelCode

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back