# Sum data

#### Howdy1

##### Board Regular
Hi,

I am trying to sum a range of data based on two critier in two columns. Below are the example,

ColA ColB ColC
1 OnShore 80
2 OffShore 20
3 OnShore 50
1 OffShore 50
1 OnShore 50

Basically, I need to report for the above data with the same ColA and ColB criteria. I could get to the result with the concatenation with sumif(). I wonder if there is any better formula to summarize the result for ColC with the same ColA and ColB?

Thanks,

Howdy

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
For 1 and Onshore try

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="Onshore"),C1:C10)

Thanks for the answer. But I tested and it return 0 even though there is data that meets the criteria. I read the formula definition and it seems trying to multiply all arrays. In my case, since colB is a nonnumberic data, it wil assign 0. I assume that that is why I got 0 in my result.

Make sure that column B doesn't contain leading or trailing spaces. Here's the result I get with your sample data:

<b>Sheet5</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:61px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td >OnShore</td><td style="text-align:right; ">80</td><td > </td><td style="text-align:right; ">130</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td >OffShore</td><td style="text-align:right; ">20</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td >OnShore</td><td style="text-align:right; ">50</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1</td><td >OffShore</td><td style="text-align:right; ">50</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">1</td><td >OnShore</td><td style="text-align:right; ">50</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(A1:A10=1)</span>,--<span style=' color:008000; '>(B1:B10="Onshore")</span>,C1:C10)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

You could also use a Pivot Table to get all the values at once.

lenze

P.S. Take a look at Pivot Tables

You could also use a Pivot Table to get all the values at once.

lenze

Thanks. Just curious, Why the -- before the array?

Thanks. Just curious, Why the -- before the array?
The argument (A1:A10=1) evaluates to TRUE/FALSE. The -- is used to Coerce the result to 1/0. You could also use (A1:A10=1)+0 or (A1:A10=1)*1

lenze

Replies
11
Views
857
Replies
15
Views
1K
Replies
1
Views
493
Replies
3
Views
316
Replies
6
Views
148

1,196,266
Messages
6,014,333
Members
441,816
Latest member
Klingon1960

### 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.

### Which adblocker are you using?

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

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