Dear All
I would like to find the maximum of column totals by dates:
In the example below, for 01/08/21
Count1 = 1 + 0 + 1 = 2
Count2 = 1 + 1 + 1 = 3
Count3 = 1 + 0 + 0 = 1
Count4 = 0 + 0 + 0 = 0
MaxCount = MAX(2,3,1,0) = 3
Thank you!
Maximum of sum of column totals by dates.png
Try this:
Formula:
Please Login or Register to view this content.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thank you Aardigspook for your answer.
I forgot to mention.. there are more than 50 columns from Count1 to Count50 in my actual workbook.
Last edited by josephteh; 08-20-2021 at 06:45 AM.
try
=MAX(SUMIF(Table1[[#All],[Date]],[@Date],Table1[[#All],[Count1]]),SUMIF(Table1[[#All],[Date]],[@Date],[Count2]),SUMIF(Table1[[#All],[Date]],[@Date],[Count3]),SUMIF(Table1[[#All],[Date]:[Count1]],[@Date],Table1[[#All],[Count4]]))
Copy to G4, G7, G12 etc
Just seen your comment re 50 COUNTS! (just a minor oversight).
Probably VBA then ?
Is format the same with COUNT1 in column C ?
Last edited by JohnTopley; 08-20-2021 at 06:45 AM.
Thanks JohnTopley for your answer.
As mentioned above, I forgot to mention.. there are more than 50 columns from Count1 to Count50 in my actual workbook.
Please try
=IF(B3=B4,"",MAX(MMULT(TRANSPOSE(B4:INDEX(B4:B$24,MATCH(B4,B4:B$24))^0),C4:INDEX(F4:F$24,MATCH(B4,B4:B$24)))))
Ctrl+Shift+Enter
Thank you, Bo_Ry! Works perfectly! Possible to have non-array formula?
Try this normal enter
=IF(B3=B4,"",MAX(MMULT(COLUMN(A$1:INDEX($1:$1,MATCH(B4,B4:B$24)))^0,C4:INDEX(F4:F$24,MATCH(B4,B4:B$24)))))
if the above formula does not work with Normal enter, try below.
=IF(B3=B4,"",MAX(INDEX(MMULT(COLUMN(A$1:INDEX($1:$1,MATCH(B4,B4:B$24)))^0,C4:INDEX(F4:F$24,MATCH(B4,B4:B$24))),)))
Thank you, Bo_Ry! Perfect!
Sorry, Bo_Ry.. need your further help! If I insert columns between Count3 and Count4, both the array and non-array formulas show #VALUE! errors.
I don't know how to adapt the formulas.
MMULT doesn't like blank
fill number to the insert column or add --
Try
=IF(B3=B4,"",MAX(MMULT(COLUMN(A$1:INDEX($1:$1,MATCH(B4,B4:B$24)))^0,--C4:INDEX(F4:F$24,MATCH(B4,B4:B$24)))))
Please try below array formula in G4:
Formula:Please Login or Register to view this content.
if you insert columns into the table, just change this part [ROW($1:$4)] to the total column numbers of your count header fields. for example: ROW($1:$50) for 50 columns
Row row row your boat
Gently down the stream
Thanks Metoo7, it works! I have adapted your formula to the following so I may insert columns before Count4 without having to change the formula:
Please Login or Register to view this content.
Oh, by the way, I have to CSE the formula.
Metoo7, one more thing.. is it possible to make the formula to SUM, rather than COUNT? Because the values are not just 0 or 1, it may also be other integer numbers. Thank you.
That's good.
You may use SUMIFS as below.Originally Posted by josephteh
Formula:Please Login or Register to view this content.
Cell G4 formula , Drag down
HTML Code:
Thank you, another brilliant formula!
You're Welcome. Glad to help . Thank You for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks