SumIf formula help

Edgie

Board Regular
Joined
Aug 5, 2011
Messages
102
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,222,397
Messages
6,165,763
Members
451,985
Latest member
jchunowitz

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