Sumifs function

Question

Quantity SoldProduct&nbsp;5Apples&nbsp;4Apples&nbsp;15Artichokes&nbsp;3Artichokes2722Bananas&nbsp;12Bananas6110Carrots&nbsp;33Carrots&nbsp;FormulaDescription&nbsp;27Adds the number of products that begin with A . It uses the wildcard character * in Criteria1, "=A*" to look for matching product names in Criterial_range1 B2:B9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 27.&nbsp;0Adds the number of products that begin with A &amp; B. It uses the wildcard character * in Criteria1, "=A*" &amp; "=B*" to look for matching product names in Criterial_range1 B2:B9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 61.&nbsp;Second Formula gives answer 0 but should be 61.&nbsp; Is this due to having 2 Criteria1's but only 1 Criterial_range1?&nbsp; I need help.&nbsp; Thanks, Ray

lorenzo kim · Answer

try:=SUMIF(B2:B9,"=A*",A2:A9)+SUMIF(B2:B9,"=B*",A2:A9)HTH

detlef_lewin · Answer

Or a bit shorter:=SUM(SUMIFS(A2:A9,B2:B9,{"A*";"B*"}))&nbsp;

lorenzo kim · Answer

neat!

Forum Discussion

Sumifs function

3 Replies

Quantity Sold	Product
5	Apples
4	Apples
15	Artichokes
3	Artichokes	27
22	Bananas
12	Bananas	61
10	Carrots
33	Carrots
Formula	Description
27	Adds the number of products that begin with A . It uses the wildcard character * in Criteria1, "=A" to look for matching product names in Criterial_range1* B2:B9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 27.
0	Adds the number of products that begin with A & B. It uses the wildcard character * in Criteria1, "=A" & "=B" to look for matching product names in Criterial_range1 B2:B9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 61.