Sumproduct formula won't work after copied

[FrigidDigit]

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)

 

[NBVC]

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)

 

[FrigidDigit]

HI NBVC

Hi Thanks for the response!

Nope, I am in a summary sheet.

Any other ideas?

FD

 

[NBVC]

What doesn't work about it?

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

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

 

[FrigidDigit]

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

 

[NBVC]

...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?

 

[FrigidDigit]

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

 

[Brian from Maui]

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.

 

[NBVC]

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....

 

[FrigidDigit]

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.

Thanks for your time!

FD