Please see attached file...
I would like a formula that sums the "amount" of each bucket type.
The Quantity of items under each bucket change often and the arrangement of Buckets change as well.
Example 1
US
10 entries
Mexico
5 entries
Canada
7 entries
Example 2
Canada
12 entries
US
4 entries
Mexico
20 entries
ect
I hope this makes sense.
Thanks
test.xlsx
I made a few changes....I remove the merge cells (the bane of XL existence)...I changed it to a format -alignment horizontal across cells. This makes it look like a merged cell but isn't. I also added the Country header to all the cells and then change the font color to white to "hide" them. Then I did a SUMIFS, a SUMIF probably would have worked also...HTH....
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
thank you for your reply.
This doesn't work for my situation for the number of entries under each bucket can be a different size and the bucket arrangement can change. I would like to avoid typing in US.
The way I would approach this is with a helper column to fill in the "missing" bucket names.
In H4, copied down...
=IF(C4="",H3,C4)
Then in O4, copied down...
=SUMIF($H$4:$H$24,"*"&N4,$G$5:$G$24)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
first unmerge the cells
or you could use copy paste below in O4 hold control and shift together and then hit enter to make it array formula and then drag down
=SUM(INDIRECT("F"&MATCH(N4,SUBSTITUTE($C$1:$C$100,"Bucket: ",""),0)+1):INDIRECT("F"&MATCH(TRUE,INDIRECT("F"&MATCH(N4,SUBSTITUTE($C$1:$C$100,"Bucket: ",""),0)+1&":F100")="",0)+MATCH(N4,SUBSTITUTE($C$1:$C$100,"Bucket: ",""),0)+1-2))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
FDibbins! that did it! Thanks
you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks