SUMPRODUCT question, finding data on multiple criteria

[Guest]

Hi, I am trying to retrieve data from one worksheet (QBData_Feb) and dump it
into a template on a separate worksheet (CLC_Feb_Commissions). The
QBData_Feb worksheet is organized in rows where I am trying to find sales
figures (QBData_Feb!I4:I500) for various products based on two criteria
(QBData_Feb!B4:B500 and QBData_Feb!C4:C500). The criteria are designated in
the template worksheet and for the example below, are in column A (A7), and
in row 4 (J4).

There are many situations where the criteria combination could specify more
than one row/record in the QBData worksheet, so I need to sum the totals.
I've tried using this formula:

=SUMPRODUCT((QBData_Feb!B4:B500=A7),(QBData_Feb!C4:C500=J4),(QBData_Feb!I4:I500))

Problem is, I am getting a #N/A result despite the fact that there is
definitely valid data.

If anyone has thoughts as to what I am doing wrong or has a better solution,
I would greatly appreciate the insight. Thanks!

 

[T. Valko]

Problem is, I am getting a #N/A result despite the fact that there is

definitely valid data.

Do you have any #N/A "errors" in any of those ranges?

Try it like this:

=SUMPRODUCT(--(QBData_Feb!B4:B500=A7),--(QBData_Feb!C4:C500=J4),QBData_Feb!I4:I500)

Biff