How to sum if the data is in multiple column and rows - i have tried using sumproduct, sumifs and index and match functions to no avail.

[sunitaaggarwal]

How to sum if the data is in multiple column and rows - i have tried using sumproduct, sumifs and index and match functions. but nothing is working for me.
I want to get sum of column 84341 and for row1226. Please any one guide me

 

[Zot]

I can't see what excel column up there. So, I made a simple table

Book1.xlsm
	E	F	G	H	I	J
3		84341	74341	84341	84341
4	1226 Big Brand Sales	1	2	3	4	8
Sheet9

Cell Formulas
Range	Formula
J4	J4	=SUMPRODUCT((F3:I3=F3)*F4:I4)

 

[Saba Sabaratnam]

If you want to apply criterial for rows (e.g. 1226 Big Brand Sales) and columns (E.g. 74341), you may want to use the following formula.

Enter the following formula in Cell B13

=SUMPRODUCT(($B$1:$E$1=$B$10)*($A$2:$A$4=$B$11)*(B2:E4))

Kind regards

Saba

 

[Zot]

My bad. I did not see you will also have multiple 1226 in rows

 

[sunitaaggarwal]

My bad. I did not see you will also have multiple 1226 in rows

Hi zot, thanks for your help. i used sumproduct, its working for me now

 

[sunitaaggarwal]

If you want to apply criterial for rows (e.g. 1226 Big Brand Sales) and columns (E.g. 74341), you may want to use the following formula.

View attachment 29255

Enter the following formula in Cell B13

=SUMPRODUCT(($B$1:$E$1=$B$10)*($A$2:$A$4=$B$11)*(B2:E4))

Kind regards

Saba

Thanks saba