Combine index match and sumproduct

Esrei · 07-05-2006, 10:10 AM

Is it possible to combine sumproduct with index or match/

I have a list of customers with area codes in A, C is overdue amounts D
amounts payable the end of the current month and E payable next month. The
headings for c,d,e is dates.
How do I tell the formula to sumproduct a column, but the column varies.
My summayr is a forcast of when recievables is due with area codes in A
descriptions in B and dates from C-AZ.
Sorry I think I have confused myself by now

Franz Verga · 07-08-2006, 10:45 AM

Esrei wrote:
> Is it possible to combine sumproduct with index or match/
>
> I have a list of customers with area codes in A, C is overdue amounts
> D amounts payable the end of the current month and E payable next
> month. The headings for c,d,e is dates.
> How do I tell the formula to sumproduct a column, but the column
> varies. My summayr is a forcast of when recievables is due with area
> codes in A descriptions in B and dates from C-AZ.
> Sorry I think I have confused myself by now

I think it could be better if you could upload a small example file to
www.savefile.com

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

**daddylonglegs** · 07-08-2006, 12:22 PM

A small example

If you had customers listed in column A and amounts in columns B to F with date headers in B1:F1 then you could sum amounts for a specific customer for a specific date with this formula

=SUMPRODUCT(--(A2:A100=H1),INDEX(B2:F100,0,MATCH(H2,B1:F1,0)))

where H1 contains the customer name and H2 the date to sum for.

**DRFJR** · 11-22-2013, 09:05 PM

Hello
I am using a similar formula to pull data for a weekly efficiency report, see below.
{=SUMPRODUCT(--(ALVA!B9:B49=P4),INDEX(ALVA!P9:P49,0,))/SUMPRODUCT(--(ALVA!B9:B49=P4),INDEX(ALVA!L9:L49,0))}

The formula works great, but I get a #DIV/O error if it references a cell that it blank.
How can I hide this error?
Thanks

**arlu1201** · 11-25-2013, 08:08 AM

DRFJR,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.