SUM every NTH ROW MEETING 2 CRITERIA

[ozbeachbum]

Hi All,
I have a formula and need to adjust it so it will meet 2 criteria.
Searching the net and trying variations with my level of expertise, has been to no avail, I would greatly appreciate any assistance.
I apologise, I would have attached a mini sheet but I am still trying to work out the installation issue since upgrading.

=SUMPRODUCT((MOD(ROW(S70:S333)-70,6)=0)*(J70:J333="CBD"),(S70:S333))

I need the formula to also include that it only SUM cells that have a positive figure.
Cheers,
Dave.

 

[ozbeachbum]

Hi All,
To save everyones time, problem solved.
Dave.

 

[Peter_SSs]

Good to hear you solved the problem.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[kvsrinivasamurthy]

Try this

=SUMPRODUCT((MOD(ROW(S70:S333)-70,6)=0)*(J70:J333="CBD")*(S70:S333>0)*(S70:S333))

 

[ozbeachbum]

Good to hear you solved the problem.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Thanks for the heads up.
Will do so in the future.

 

[ozbeachbum]

Try this

=SUMPRODUCT((MOD(ROW(S70:S333)-70,6)=0)*(J70:J333="CBD")*(S70:S333>0)*(S70:S333))

Thanks,
It works perfectly.
Cheers,
Dave.

 

[Peter_SSs]

I'm not sure what might happen with your worksheet in the future, but if any rows are added/deleted above row 70 the formula will likely return incorrect results. If that is any possibility you could make the formula more robust like this.

=SUMPRODUCT((MOD(ROW(S70:S333)-ROW(S70),6)=0)*(J70:J333="CBD")*(S70:S333>0)*(S70:S333))