is there any option using sumif or any other formula:- I want to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red 5
apples B red 20
apples C red 15
bananas A green 10
apples B red 50

If FRUITS begins in A2, GRADE in B2, etc, use this:
=SUMPRODUCT((A3:A7="apples")*(B3:B7="b")*(C3:C7="r ed")*(D3:D7))
easier if you use cells containing the criteria you want to sum, in lieu
of changing the formula each time.
enter apples, b, red in F1, G1, H1, respectively, then use this
formula: =SUMPRODUCT((A3:A7=f1)*(B3:B7=g1)*(C3:C7=h1)*(D3:D 7))
change the values in F1, G1, H1 to suit your tastes in fruit!




Mubashar wrote:
is there any option using sumif or any other formula:- I want to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red 5
apples B red 20
apples C red 15
bananas A green 10
apples B red 50

Use the following formula. It assumes that your data is in
A2:D10.

=SUMPRODUCT(--(A2:A10="apples"),--(B2:B10="B"),--(D2:D10))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mubashar" wrote in message
...
is there any option using sumif or any other formula:- I want
to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red
5
apples B red
20
apples C red
15
bananas A green 10
apples B red
50

"Mubashar" wrote:

is there any option using sumif or any other formula:- I want to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red 5
apples B red 20
apples C red 15
bananas A green 10
apples B red 50

For multiple conditions, use the array formula sum(if . For example, if you
want to sum the range QTY if the range Grade = A AND if the range Colour =
red, do this: =sum(if((range="condition")*(range="condition"),ra nge))
Then simultaneously press Ctrl,Shift,Enter.
Sumproduct stops working on a large range (about 220), but sum(if seems
unlimited by range size.

Hi there,
Here is a neat function to sum data using multiple criteria. In
addition, it also groups and sums values based on your need.



Option Compare Text

Function XSumIf(myRange As Range, All, ParamArray vals())

'XSumIf groups and sums data, in the last column, based on n number of
conditions.
'The user may replace a criteria with a text to indicate to the
function to group values
'through the "All" argument. Let's go directly to an example:

'Car Yellow US 15
'Truck Yellow US 10
'Truck Red France 8
'Bike Yellow US 6

'=XSumif(A1:D4,"All", "Car", "Yellow", "US") === 15
'=XSumif(A1:D4,"All", "All", "Yellow", "US") === 31
'=XSumif(A1:D4,"*", "Truck", "*", "*") === 18

'SQL users! The function works similar to a "group by query".


Dim i, j, r, c As Long
Dim vArray As Variant
Sum1 = 0
Sum2 = 0
r = myRange.Rows.Count
c = myRange.Columns.Count
vArray = Range(myRange.Address).Value

For i = 1 To r

For j = LBound(vals, 1) To UBound(vals, 1)
If vals(j) = All Then
Sum2 = vArray(i, c)
ElseIf vals(j) = vArray(i, j + 1) Then
Sum2 = vArray(i, c)
Else
Sum2 = 0
Exit For
End If
Next j

Sum1 = Sum1 + Sum2

Next i

XSumIf = Sum1

End Function

' by Samy Zeraouli

I used sum if , but the result is 0, i was cheking my formula and I don't
know what I did wrong.....

"John" wrote:



"Mubashar" wrote:

is there any option using sumif or any other formula:- I want to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red 5
apples B red 20
apples C red 15
bananas A green 10
apples B red 50

For multiple conditions, use the array formula sum(if . For example, if you
want to sum the range QTY if the range Grade = A AND if the range Colour =
red, do this: =sum(if((range="condition")*(range="condition"),ra nge))
Then simultaneously press Ctrl,Shift,Enter.
Sumproduct stops working on a large range (about 220), but sum(if seems
unlimited by range size.

Thanks Chip, That's a really useful one there!!!

"Chip Pearson" wrote:

Use the following formula. It assumes that your data is in
A2:D10.

=SUMPRODUCT(--(A2:A10="apples"),--(B2:B10="B"),--(D2:D10))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mubashar" wrote in message
...
is there any option using sumif or any other formula:- I want
to sum apples
of Grade "B" QTY (multiple criteria)

FRUITS GRADE COLOUR QTY
apples A red
5
apples B red
20
apples C red
15
bananas A green 10
apples B red
50