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]

Try Colo's Html Maker. There is a link at bottom of this page or you can get it through the Mr.Excel FAQ page: http://www.mrexcel.com/board2/faq.php

 

[FrigidDigit]

I see what you're saying NBVC!

My formula requires that the max date value for the entire column appears next to the subname.

What do I need to change if I just want the max date for that particular sub name, even if it isn't the max of all date entries?

 

[Aladin Akyurek]

...

My formula requires that the max date value for the entire column appears next to the subname.

What do I need to change if I just want the max date for that particular sub name, even if it isn't the max of all date entries?

Compiling a guess from the formula you tried (see your initial post)...

=MAX(IF('Invoice Listing'!$J$2:$J$1000=H$2,'Invoice Listing'!$E$2:$E$1000))

which uou need to confirm with control+shift+enter, not just with enter.

 

[FrigidDigit]

RTI Subcontractor Invoices Overview NonADO 0.08.xls
	A	B	C	D	E	F	G	H	I	J
1	Subcontractor	TO	Inv	BillingPeriodStart	BillingPeriodEnd	InvoiceValue(ZAR)	BillingStatus	Link	ConCate	SubName
2	Subname1	908	1	01Jun2004	31Oct2004	18,899.00	Approvedforpayment	Open	9081	Subname1
3	Subname2	908	2	01Nov2004	30Jun2005	97,081.00	Submittedforapproval	Open	9082	Subname2
Invoice Listing

 

[FrigidDigit]

Thansk Aladin will try this and let u know

FD

 

[FrigidDigit]

Nope Aladin,

This formula still just gave me the latests date in the entire list, not the latest date for that subname.

Seems like a good idea to play with tho.

FD

 

[FrigidDigit]

Sorry Aladin,

Update on my last post.

Like an idiot I forgot Ctr + SHift +Enter.

It works perfectly!

Thanks!!