# SumIf formula help

#### Edgie

##### Board Regular
Hi Everyone,

I have 5 tabs of data, an example of one tab of data is like this, each tab corresponds to a day.

(A1)Red (B1) Blue (C1) Yellow (d1) Total

(A2) 5 (B2) 10 (C2) 7 (d2) 22

(A3) 5 (B3) -10 (C3) -3 (D3) -8

I have a summary tab which i would like to do the sum if statement

I want to summarise the data but the criteria is if the Total cell is bigger then 0.

so i want to sum all the reds (including all of the tabs) only if the total is bigger then 0 for the line of data.

So for Reds on A2 i want to sum all the reds up but if only on that day the total is bigger then 0.

Hope that makes sense....

thanks

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Use the SUMIFS functions instead. establishing named ranges for your columns will also make the formulas easiers.

Cell.Value = SUMIFS(red1, total1, ">0")+SUMIFS(red2, total2, ">0"), etc.

In the above formula, red1 refers to the red column of sheet 1, total1 refers to the total column in sheet 1, the number goes up for each sheet.

Without named ranges it would look something like, assuming the formula is on sheet1:

=SUMIFS(A2:A10,D2:D10,">0")+SUMIFS(Sheet2!A2:A10,Sheet2!D2:D10,">0"), etc

Make sense?

Hey, thanks very much for the quick response!!

Sorry i didn't explain it very well, i will try again.

Tab Day 1

(A1)Month (B1) Red (C1) Blue (D1) Total
Nov-11 10 20 30
Dec-11 10 -40 -30

So the Summary Sheet will be
(A1)Month (B1) Red (C1) Blue (D1) Total
Nov-11 10
Dec-11 0

Obviously this is only looking at one tab of data but there will be numerous, so im looking at a specific line of data, so in this case is the month or the same cell line on the different tabs but then also summing the ones when the total is bigger then 0.

I think your example wasn't looking at specific line of data it was summing the whole range.

Hope that is a little bit more clear....thanks for your help

SUMIFS will the sum the column but it will only use the data that meets the criteria specified:

=SUMIFS(A2:A10,D2:D10,">0")

will sum every number in Range A2:A10 where the value in colum D of the same row is greater than zero.

In you new example you've added a column with dates... if you want to sum only the red values in a row where the month is Nov-11 and the total is greater than zero it would be:

=SUMIFS(B2:B10,A2:A10,"Nov-11",D2:D10,">0")+[same formula referencing Sheet2],etc.

You'll need to make sure the date syntax is compatible but otherwise you get the idea.

Or am I totally missing what you're trying to do?

I want to summarise the data but the criteria is if the Total cell is bigger then 0.

so i want to sum all the reds (including all of the tabs) only if the total is bigger then 0 for the line of data.

Brilliant.....thanks very much for your help!!

Works a treat, i just assumed because i saw a range value it would sum the range but it's worked brilliantly....thanks again for your help

Replies
5
Views
138
Replies
5
Views
171
Replies
7
Views
168
Replies
6
Views
971
Replies
4
Views
419

1,203,045
Messages
6,053,187
Members
444,644
Latest member
keepontruckinc4

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