Sum certain row with sumif formula.

[asyamonique]

Hello,
How can I change the sum ranges with given formula below?
Its lıke I wanna calculate all row instead of a column.

Apple

1

2

3

4

5

6

7

8

9

Apple= 45

Many Thanks

=SUMIF(A:J,K2,B:B)

 

[jtakw]

Hi,

Is this what you mean:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Apple	1	2	3	4	5	6	7	8	9
2	Orange	2		4		6					Apple
3	Apple		2			5			8		84
4	Apple				11					13
5
Sheet949

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT((A1:A10=K2)*B1:J10)

 

[asyamonique]

Hi,

Is this what you mean:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Apple	1	2	3	4	5	6	7	8	9
2	Orange	2		4		6					Apple
3	Apple		2			5			8		84
4	Apple				11					13
5
Sheet949

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT((A1:A10=K2)*B1:J10)

[/RA

Cell Formulas
Range	Formula

Hi, Yes exactly, thanks a lot. Is there anyway to remove ranges like

=SUMPRODUCT((A:A=K2)*B:J)

 

[jtakw]

Hi,
Yes exactly, thanks a lot.
Is there anyway to remove ranges like

=SUMPRODUCT((A:A=K2)*B:J)

[/range]

Yes you can, but not recommended, unless you absolutely need to, there are Over a Million rows in excel, so 9 Columns and over 1 Million rows, that formula will need to perform over 9 Million calculations, depending on what else you have on your sheet, it will impact performance.

Maybe limit the rows to something you Know is more than enough, like 5000, 10000, 50000, 100000, etc.

 

[asyamonique]

Yes you can, but not recommended, unless you absolutely need to, there are Over a Million rows in excel, so 9 Columns and over 1 Million rows, that formula will need to perform over 9 Million calculations, depending on what else you have on your sheet, it will impact performance.

Ok I will keep it as 1000rows that's much enough for me.
Cheers