Sum of data of a row with criteria of 2 other rows. Excel 2003

htevents

New Member
Joined
Sep 14, 2017
Messages
5
I have a sheet that looks something like this:

A B C D
1 Brand Dealer Type Amount

2 Lesli HD Cake 2

3 Rubro PL Cake 1

4 Zena PL SS 3

5 Zena HD Cake 2

6 Rubro PL SS 12


Totall Cakes HD: 4
Totall Cakes PL: 1
Totall SS HD: 0
Totall SS PL: 15



This is not a Pivot Table

What I want is to know the total amount of a specific type of a specific dealer. So in this example: the totall amount of cakes from dealer "HD" is 4. What formula can I use for this?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
one way maybe...

Unknown[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Brand[/TD]
[TD]Dealer[/TD]
[TD]Type[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Lesli[/TD]
[TD]HD[/TD]
[TD]Cake[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Rubro[/TD]
[TD]PL[/TD]
[TD]Cake[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Zena[/TD]
[TD]PL[/TD]
[TD]SS[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Zena[/TD]
[TD]HD[/TD]
[TD]Cake[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Rubro[/TD]
[TD]PL[/TD]
[TD]SS[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD]HD[/TD]
[TD]PL[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Cake[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]SS[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]=SUMPRODUCT(($B$2:$B$6=B$8)*($C$2:$C$6=$A9),$D$2:$D$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks for the try, but it gives me an error when I paste your formula in the cell editor and then press CTRL+SHIFT+ENTER or with a normal ENTER
 
Upvote 0
what specifically is the error you are getting?

the formula wouldn't require control shift enter
 
Upvote 0
I got it:)
This is the formula I needed: =SUMPRODUCT((B2:B6="HD")*(C2:C6="Cake")*D2:D6)

Almost the same as your formula, but I think my version of Excel needed the * instead of the , to multiply by the data in D.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top