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...

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...

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...