SUMIF of specified cells in a range

  • Thread starter Thread starter Bretter99
  • Start date Start date
B

Bretter99

I have a column of data that i want to use SUMIF>0 but its not over the whole
range, just every 5th row for instance...

a1=5
a6=8
a11=-5
a16=2
a21=-22

SUMIF only seems to work with a continuous array of cells not targetted
cells, any ideas how to get around this? (I just need to sum the non negative
figures)
something like SUMIF(a1,a6,a11,a16,a21)>0 or something like that...

Thanks

Brett
 
What do you have in the other cells in column A, i.e. in a2, a3, a4
etc?

If they are empty then you could use the range A1:A21.

Hope this helps.

Pete
 
Enter the following formula in column B and SUMIF column B:
=IF(MOD(ROW(),5)=0,A1,"")
 
Or even this if you need to ignore negative numbers also:
=IF(AND(MOD(ROW(),5)=0,E10>=0),E10,"")
 
sorry, just to clarify...

the worksheet is more like this:

A B c
customer 1 PPM Points 200
customer 1 rating 95
customer 1 performance 98%
customer 1 complaints 2
customer 1 deliveries 8
customer 2 PPM Points -25
customer 2 rating 2
customer 2 performance 22%
customer 2 complaints 11
customer 2 deliveries 15

The customer 1, customer 2's are merged into one cell and the list is 20
customers long. All i need is to sum the negative points from every 5th row
in row C to give me a total amount of negative PPM points for all 20
customers so we can see how many demerits we lost, hence i cant look at
column C as a single array, i need to cherry pick the cells to sum. Hope this
makes sense, and i'm using excel 2000 if that makes a difference.

Regards

Brett
 
Add formula in column D as follows:
=IF(AND(MOD(ROW(),5)=0,C1<0),C1,"")
The SUM column D.
 
Columns C,D,E,F,etc... are for jan, feb, mar and so on so i cant really
utilise column D. I need the calc to be at the bottom of the list looking up
the column so that at the bottom of the grid of data i can put a calc in cell
'C200' for example and drag it right across 12 cells so i have the total
demerits for each month in cells C200, D200, E200, etc.... sorry if my
descriptions are not very clear....

Brett
 
Back
Top