Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with criteria
Hi,
I need to sumif the amount with the criteria of segment 'Cafe' and product 'mayo'. I am using this function with the sheetname 'cafe S MTD07'. I should get a return amount of 30 instead of 60. SUMIF('cafe S MTD07'!A:A,B1,'cafe S MTD07'!C:C) A B C Segment Product Amount 1 Cafe Mayo 10 2 Cafe Mayo 20 3 Hotel Mayo 30 4 Cafe Pudding 30 Pls enlighten me... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with criteria
I m able to find the solution using sumproduct.
=SUMPRODUCT(('cafe S MTD07'!$C$2:$C$9999="CAFES, CAFETERIA, BAR&PUB")*('cafe S MTD07'!$H$2:$H$9999=G8)*('cafe S MTD07'!$J$2:$J$9999)) But at the end of the sumproduct, i need to total sum the value. Its return '0'. How do i make it to calculate all the total value ? "jj" wrote: Hi, I need to sumif the amount with the criteria of segment 'Cafe' and product 'mayo'. I am using this function with the sheetname 'cafe S MTD07'. I should get a return amount of 30 instead of 60. SUMIF('cafe S MTD07'!A:A,B1,'cafe S MTD07'!C:C) A B C Segment Product Amount 1 Cafe Mayo 10 2 Cafe Mayo 20 3 Hotel Mayo 30 4 Cafe Pudding 30 Pls enlighten me... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF with criteria
Try it like this:
=SUMPRODUCT(--('cafe S MTD07'!A1:A4="cafe"),--('cafe S MTD07'!B1:B4="mayo"),'cafe S MTD07'!C1:C4) Note that with SUMPRODUCT you can't use entire columns as range references unless you're using Excel 2007. -- Biff Microsoft Excel MVP "jj" wrote in message ... Hi, I need to sumif the amount with the criteria of segment 'Cafe' and product 'mayo'. I am using this function with the sheetname 'cafe S MTD07'. I should get a return amount of 30 instead of 60. SUMIF('cafe S MTD07'!A:A,B1,'cafe S MTD07'!C:C) A B C Segment Product Amount 1 Cafe Mayo 10 2 Cafe Mayo 20 3 Hotel Mayo 30 4 Cafe Pudding 30 Pls enlighten me... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Criteria | Excel Worksheet Functions | |||
Criteria in SUMIF | Excel Worksheet Functions | |||
SUMIF with two criteria?? | Excel Discussion (Misc queries) | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
Sumif() with criteria | Excel Worksheet Functions |