I want to sum two conditions like apples of Grade "B"
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 |
I want to sum two conditions like apples of Grade "B"
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 |
I want to sum two conditions like apples of Grade "B"
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 |
I want to sum two conditions like apples of Grade "B"
"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. |
I want to sum two conditions like apples of Grade "B"
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 want to sum two conditions like apples of Grade "B"
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. |
I want to sum two conditions like apples of Grade "B"
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 |
All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com