ScreenHunter_001.jpg
B2:G9 contains 6 blocks of data with label TA, TB & TC.
B11:D16 shows the count and sum results for all the 3 labels.
What are the formulas in D11:D16 ?
ScreenHunter_001.jpg
B2:G9 contains 6 blocks of data with label TA, TB & TC.
B11:D16 shows the count and sum results for all the 3 labels.
What are the formulas in D11:D16 ?
Hi,
This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.
Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.
For instance are all the blocks always a 2x4 matrix
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
Hi Richard,
The cut file attached
Change highlighted cells if block size not 7 X 4Please Login or Register to view this content.
Last edited by JohnTopley; 01-22-2017 at 07:07 AM.
Thank you John, it works.
Any method other than running a macro please?
There may be: Is there are finite (maximum) number of matrices (blocks of 7 x 4)? and if it could be done would you accept using "helper" columns/rows?
Removed: duplicate incomplete!!!
Last edited by JohnTopley; 01-22-2017 at 11:22 AM.
Using helper columns/rows: Sheet1
In AV2: Calculates the sum of numbers in each matrix
=IFERROR(SUMPRODUCT((OFFSET($A$1,($AU2-1)*7+1,(AV$1-1)*4+1,7,4))),"")
Copy across and down
In AV5: determines the "flag" i.e. "TA" etc
=OFFSET($A$1,($AU5-1)*7+7,(AV$1-1)*4+1,1,1)
in Av8: counts the number of non-zeros in each matrix
=IFERROR(COUNTIF((OFFSET($A$1,($AU8-1)*7+1,(AV$1-1)*4+1,7,4)),">0"),"")
in Aw12
=SUMPRODUCT(--($AV$5:$BF$6=$AV12)*($AV$8:$BF$9))
Copy down
in AX12
=SUMPRODUCT(--($AV$5:$BF$6=$AV12)*($AV$2:$BF$3))
Copy down
Te attached has "helper" calculations in Sheet3 and results table in Sheet2.
I don't mind using helper. I'll try when I'm available and give you reply.
Thanks John.
See post #9.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks