SumIf formula help

[Edgie]

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

 

[firefiend]

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?

 

[Edgie]

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

 

[firefiend]

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.

 

[Edgie]

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