Sum_Range with multiple rows and columns

[VonRobbo]

Greetings.

I'm basically trying to do a SUMIFS statement, but instead of only summing single cells in a row or column when a certain criteria is met, I want to sum a whole row of cells when a criteria is met.

For example I want to be able to do something like:
=SUMIFS(B2:E10,A2:A10,"Apples")

If A3 = "Apples, I will get the sum of all cells from B3:E3.

When I do a formula like the above, I get a #VALUE! result.

This seems like an easy thing that I'm sure someone must have done before. Any assistance is greatly appreciated.

 

[Toadstool]

Hi VonRobbo,

Does this work?

Book1
	D	E	F	G	H
1	Total=	265
2	Pears	2	21	64	87
3	Apples	56	56	35	13
4	Oranges	59	97	21	34
5	Lemons	58	2	96	62
6	Pears	13	16	7	64
7	Apples	21	24	15	45
8	Oranges	7	50	43	29
9	Lemons	11	92	60	30
10	Bananas	8	14	74	75
Sheet1

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(E2:H10*(D2:D10="Apples"))

 

[ngoctinh87]

Greetings.

I'm basically trying to do a SUMIFS statement, but instead of only summing single cells in a row or column when a certain criteria is met, I want to sum a whole row of cells when a criteria is met.

For example I want to be able to do something like:
=SUMIFS(B2:E10,A2:A10,"Apples")

If A3 = "Apples, I will get the sum of all cells from B3:E3.

When I do a formula like the above, I get a #VALUE! result.

This seems like an easy thing that I'm sure someone must have done before. Any assistance is greatly appreciated.

=SUM(SUMIF(A2:A10,"Apples",OFFSET($B$2,,{0,1,2,3})))
Or
=SUM(MMULT(N(A2:A10="Apples")*B2:E10,{1;1;1;1}))