I have this long winded formula that is copied down each cell in its column
=IF([@[Month Num]]<>Y4,SUMIF(Y:Y,[@[Month Num]],P:P),"")
I often have some rows hidden, I'm looking for the correct way to adjust this formula so that it does not calculate hidden rows.
any help appreciated
Try the AGGREGATE() function -- which has an option to ignore hidden cells: https://support.office.com/en-us/art...6-e19993fa26df
Originally Posted by shg
How/where would i place it in the above mention formula. I understand the basics of this function in a simple formula on its own, but dont have a clue as to how to add it to my complex formula
You do not need the complex formula. As MrShorty pointed out, you can use the Aggregate formula. Alternatively, you can use the SubTotal Formula.
Suppose, you have your data in between B2 and B5, you can place the SUBTOTAL or the AGGREGATE formula in a cell where you want the total to be displayed.
=SUBTOTAL(9,B2:B5)
=AGGREGATE(9,1,B2:B5)
Please see the attached screenshot for sample implementation
Attachment 676933
Last edited by ExcelTips_2020; 05-10-2020 at 12:39 AM.
I would expect to use the AGGREGATE() function in place of your SUMIF() function.
Another idea -- would this work better as a pivot table where you can sum and filter in one operation?
@jojo101
off topic.
You still did not answer to your question below.
https://www.excelforum.com/excel-for...-required.html
There is no rule that states that threads have to be marked as solved, although we much prefer it if they are.
If you are satisfied with the solution, please mark that question solved.
Last edited by oeldere; 05-10-2020 at 03:42 AM. Reason: changed marking thtread solved
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks