ExcelBanter - Problem with SUMPRODUCT and Arrays

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Problem with SUMPRODUCT and Arrays (https://www.excelbanter.com/excel-worksheet-functions/65149-problem-sumproduct-arrays.html)

Problem with SUMPRODUCT and Arrays

Here is the formula I am trying to use on a worksheet called Reports.

=SUMPRODUCT(('M&I'!A3:A18=Data!E2)*('M&I'!B3:B20=R eports!B19)*'M&I'!E3:E36)

Data Key:

(Array1)
'M&I'!A3:A18=column of dates
Data!E2=Today()

(Array2)
'M&I'!B3:B20=column of customer names
Reports!B19=list box where the users can select a customer name

'M&I'!E3:E36=I want to total this column based on the criteria matched
by the arrays.

An example would be
where (date=1/13/06 and customer=ME) amount=$100.00

Thanks in advance!

Robert Palmer
Sr. Systems Specialist
VECTORsgi, INC

Problem with SUMPRODUCT and Arrays

Your arrays must be the same size to use SUMPRODUCT. Though each of
the three starts on the same row they all end at different rows
indicating that some are longer than the others.

- John Michl

Problem with SUMPRODUCT and Arrays

John,

Thank you so much! That was the issue. When selecting my ranges I
just grabbed a few cells from each row that I needed.

Robert