DangerMan88
New Member
Hello,
I am working on creating a skill inventory for the company I work for, in which I have run into a problem trying to use a formula in Excel.
I am trying to retrieve the sum of a range of cells based on the conditions that it is every 3rd row as well as the value of those cells is greater than 1.
This is the formula I am trying to use:
=SUMIF(H9:H96,AND(">1",MOD(ROW(H9:H96),3)=0),H9:H96)
When I break it down, it all makes sense in my head...
SUMIF(range, criteria, [sum_range])
So the range I would like to insert into the function is H9:H96
The criteria for that range to be evaluated in this function is that it has to be greater than 1 AND the remainder of the row number divided by 3 is equal to 0 (so it is every 3rd row).
And lastly I put in the range I want to sum, in this case it is the same range of the criteria.
There problem seems to lie somewhere within the MOD function, as if I remove this it will give me the sum of all of the cells that are greater than 1, but it will not work when I try for both conditions.
Can anyone help me with this, I cannot seem to get it to do what I am looking for.
I am working on creating a skill inventory for the company I work for, in which I have run into a problem trying to use a formula in Excel.
I am trying to retrieve the sum of a range of cells based on the conditions that it is every 3rd row as well as the value of those cells is greater than 1.
This is the formula I am trying to use:
=SUMIF(H9:H96,AND(">1",MOD(ROW(H9:H96),3)=0),H9:H96)
When I break it down, it all makes sense in my head...
SUMIF(range, criteria, [sum_range])
So the range I would like to insert into the function is H9:H96
The criteria for that range to be evaluated in this function is that it has to be greater than 1 AND the remainder of the row number divided by 3 is equal to 0 (so it is every 3rd row).
And lastly I put in the range I want to sum, in this case it is the same range of the criteria.
There problem seems to lie somewhere within the MOD function, as if I remove this it will give me the sum of all of the cells that are greater than 1, but it will not work when I try for both conditions.
Can anyone help me with this, I cannot seem to get it to do what I am looking for.
