$ every 6th row * # every 6th row base on a criteria every 6th row

[ozbeachbum]

Hi all,
Any help with the following would be greatly appreciated.

$ every 6th row * # every 6th row base on a criteria every 6th row
D70 is the first cell containing criteria (Criteria is >0) Last cell D328
Q73 is the first cell containing $ Last cell Q331
Q72 is the first cell containing # Last cell Q330

I need each $ * # if >0 and the results add together.
EG:
D70 >0 Q73 $10 * Q72 5 = $50
D76 >0 Q79 $15 * Q78 2 = $30
D82 <0 Q85 $12 * Q84 2 = $00
The result would be $80

The cell with the result in it is in one workbook and the data cells in another.
The cells between the cells with the required data, may contain; blanks; zeros; formulae or other $ and #

Cheers,
Dave.

 

[awoohaw]

May i suggest you post your data using the Mr. Excel xl2bb add in (link below) which allows you to share you data as a mini work sheet? In case you cannot do that, then please post your data as a table. Posting an image to completely recreate your scenario can have typographical or understanding errors which will give you an incorrect solution and waste the forum''s time.

Please help the forum help you.

Thanks in advance.

 

[Peter_SSs]

I have done this in a single sheet but if I have understood what you have/want then the formula would easily translate to multiple worksheets/workbooks.
I have highlighted the mentioned cells for easy viewing/checking for myself.
Wasn't quite sure of the meaning regarding column D so I have done two scenarios for that. Hopefully one of them is what you meant.

23 11 25.xlsm
	D													Q	R	S
70	8															80
71
72	0													5
73	-8													10
74	3
75														5
76	2													3
77														2
78	hjg													2
79														15
80	ghfh
81														-5
82	-5													3
83	-3
84														2
85														12
Calc 1

Cell Formulas
Range	Formula
S70	S70	=LET(s,SEQUENCE((ROW(D328)-ROW(D70)+1)/6,,,6),SUM((INDEX(D70:D328,s)>0)*INDEX(Q70:Q328,s+2)*INDEX(Q70:Q328,s+3)))

23 11 25.xlsm
	D													Q	R	S
70	>0															80
71
72	0													5
73	-8													10
74	3
75														5
76	>0													3
77														2
78	hjg													2
79														15
80	ghfh
81														-5
82	<0													3
83	-3
84														2
85														12
Calc 2

Cell Formulas
Range	Formula
S70	S70	=LET(s,SEQUENCE((ROW(D328)-ROW(D70)+1)/6,,,6),SUM((INDEX(D70:D328,s)=">0")*INDEX(Q70:Q328,s+2)*INDEX(Q70:Q328,s+3)))

 

[ozbeachbum]

May i suggest you post your data using the Mr. Excel xl2bb add in (link below) which allows you to share you data as a mini work sheet? In case you cannot do that, then please post your data as a table. Posting an image to completely recreate your scenario can have typographical or understanding errors which will give you an incorrect solution and waste the forum''s time.

Please help the forum help you.

Thanks in advance.

Hi awoohaw,
Thanks for your suggestion.
I would love to be able to do so, but ever since upgrading to office 21 it does not work.
I have to this date received no reply that has been able to assist, should you know of a solution I would be happy to hear it.
Cheers,
Dave.

 

[Peter_SSs]

I have to this date received no reply that has been able to assist,

So, is my sample data nothing like yours?

I would love to be able to do so, but ever since upgrading to office 21 it does not work.

Have you tried uninstalling and reinstalling XL2BB?
Failing that, just copy paste the small col B section & tell us the range like this

This is B70:B85 from my sample data

8​

0​

-8​

3​

2​

hjg

ghfh

-5​

-3​

and do the same for column Q

 

[ozbeachbum]

I have done this in a single sheet but if I have understood what you have/want then the formula would easily translate to multiple worksheets/workbooks.
I have highlighted the mentioned cells for easy viewing/checking for myself.
Wasn't quite sure of the meaning regarding column D so I have done two scenarios for that. Hopefully one of them is what you meant.

23 11 25.xlsm
	D													Q	R	S
70	8															80
71
72	0													5
73	-8													10
74	3
75														5
76	2													3
77														2
78	hjg													2
79														15
80	ghfh
81														-5
82	-5													3
83	-3
84														2
85														12
Calc 1

Cell Formulas
Range	Formula
S70	S70	=LET(s,SEQUENCE((ROW(D328)-ROW(D70)+1)/6,,,6),SUM((INDEX(D70:D328,s)>0)*INDEX(Q70:Q328,s+2)*INDEX(Q70:Q328,s+3)))

23 11 25.xlsm
	D													Q	R	S
70	>0															80
71
72	0													5
73	-8													10
74	3
75														5
76	>0													3
77														2
78	hjg													2
79														15
80	ghfh
81														-5
82	<0													3
83	-3
84														2
85														12
Calc 2

Cell Formulas
Range	Formula
S70	S70	=LET(s,SEQUENCE((ROW(D328)-ROW(D70)+1)/6,,,6),SUM((INDEX(D70:D328,s)=">0")*INDEX(Q70:Q328,s+2)*INDEX(Q70:Q328,s+3)))

 

[ozbeachbum]

Hi Peter,
Thanks for the formula, I tested it in relation to it referencing the cells in another workbook and was not getting the correct answer. Could be something I did incorrectly, I will have to check and get back to you if I can't work it out.
I then thought I would use it referencing cells in the same workbook and it worked, the I added amounts to cells continuing on and it did not calculate those.
I have attached a print screen to assist.
Thanks again for all you assistance.
Cheers,
Dave.

 

[ozbeachbum]

So, is my sample data nothing like yours?


Have you tried uninstalling and reinstalling XL2BB?
Failing that, just copy paste the small col B section & tell us the range like this

This is B70:B85 from my sample data

8​

0​

-8​

3​

2​

hjg

ghfh

-5​

-3​

and do the same for column Q

Hi,
Thanks for your time and patience.
Re uninstalling and reinstalling yes done, no success.
It copies to the clipboard, just won't attach to the post.
Peter has come back with a formula, which I have replied to, if you are able to view that it may assist, if not let me know and I will send to you.
Thanks again.

 

[Peter_SSs]

It copies to the clipboard, just won't attach to the post.

Try enabling this option in the XL2BB ribbon menu before you click 'Mini Sheet'

I added amounts to cells continuing on and it did not calculate those.

In your first 3 groups of 6 rows, the values in column Q are in the 3rd & 4th rows of the group.
In all the other groups in your image, the values in column Q are in the 4th & 5th rows of the group. Was that a mistake? I had assumed a regular pattern.

If I move those value to rows 3 & 4 of each group then I get the 160 that you expected.

ozbeachbum.xlsm
	D													Q	R	S
70	1														1	160
71	2														2
72	3													5	3
73	4													10	4
74	5														5
75															6
76	1														1
77	2														2
78	3													2	3
79	4													15	4
80	5														5
81															6
82	0														1
83	0														2
84	0													2	3
85	0													12	4
86	0														5
87															6
88	1														1
89	2														2
90	3													10	3
91	4													5	4
92	5														5
93															6
94	1														1
95	2														2
96	3													2	3
97	4													15	4
98	5														5
99															6
100	0														1
101	0														2
102	0													2	3
103	0													12	4
104	0														5
105															6
Calc 3 (2)

Cell Formulas
Range	Formula
S70	S70	=LET(s,SEQUENCE((ROW(D328)-ROW(D70)+1)/6,,,6),SUM((INDEX(D70:D328,s)>0)*INDEX(Q70:Q328,s+2)*INDEX(Q70:Q328,s+3)))

 

[ozbeachbum]

Thanks Peter,
Quite correct my mistake.
I have also worked out that my check for the formula working in relation referencing the other workbook had to do with blanks and zeros in the formula I was using to check for the amount, fixed that and now I get the correct answer.
As always thank you so much.
Cheers.