Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT question, finding data on multiple criteria
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT question, finding data on multiple criteria
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 "TravisB" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct - multiple criteria and IF question | Excel Discussion (Misc queries) | |||
finding data with multiple criteria | Excel Discussion (Misc queries) | |||
SumProduct With Multiple criteria | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions | |||
sumproduct using multiple criteria | Excel Worksheet Functions |