Hi,
I have 2 datasets, A&B.
I would like to sum all the costs (cell c) per identifier (b) in cell D and then do the same for data set b in column E so I can compare them line by line.
The problem I have is the identifier repeats itself in columns B and F and I don't know how to handle that part.
Thanks
Please show us what you want by filling in expected results manually.
On the face of it, I don't see how this differs from the SUMIF I provided in your earlier thread.
Maybe this in D8
=SUMIF($B$8:$B$15,B8,$C$8:$C$15)
and this in E8:
=SUMIF($F$8:$F$15,B8,$G$8:$G$15)
Last edited by AliGW; 11-14-2023 at 05:00 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks Ali.
I would like the formula to ignore cell D and E if the identifier repeats itself and just returns one figure per identifier.
I've greyed out the ones I need to ignore, also amended the formula in column E to reference column G.
The workbook hasn't changed - you need to upload it again if you have amended it!
attaching a new sheet.
Try:
=IF(COUNTIF($B$8:$B8,B8)=1,SUMIF($B$8:$B$15,B8,$C$8:$C$15),"")
and:
=IF(D8="","",SUMIF($F$8:$F$15,B8,$G$8:$G$15))
or:
=IF(COUNTIF(F$8:F8,F8)=1,SUMIF($F$8:$F$16,F8,$G$8:$G$16),"")
Last edited by AliGW; 11-14-2023 at 06:17 AM.
You could try this in J8:
=LET(u,UNIQUE(B8:B15),sa,SUMIF(B8:B15,u,C8:C15),sb,SUMIF(F8:F16,u,G8:G16),VSTACK({"Identifier","Cost A","Cost B"},HSTACK(u,sa,sb)))
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
thanks, it worked, is there a way I can put column E and in the same order so I can compare the quantities line by line?
#and D, sorry typo
This from post #6 puts them in the same order:
This from post #7 creates a handy comparison table:Try:
=IF(COUNTIF($B$8:$B8,B8)=1,SUMIF($B$8:$B$15,B8,$C$8:$C$15),"")
and:
=IF(D8="","",SUMIF($F$8:$F$15,B8,$G$8:$G$15))
Could you please try to state exactly what you want at the start of the thread and MOCK IT UP in the workbook in future instead of drip feeding changes to the original request?=LET(u,UNIQUE(B8:B15),
sa,SUMIF(B8:B15,u,C8:C15),
sb,SUMIF(F8:F16,u,G8:G16),VSTACK({"Identifier","Cost A","Cost B"},
HSTACK(u,sa,sb)))
Last edited by AliGW; 11-14-2023 at 08:10 AM. Reason: Workbook added.
yes, it was not deliberate, once I started I realized my original hypothesis was wrong.
I tried to replicate the LET formula on my sheet and for some reasons I cannot make it work.
Can you please rewrite it, by flipping columns B and C, on the dataset I'm working on Cost is on column B and Identifier is on column C, I had it the wrong way round when I shared the sheet
thank you
I keep asking you to manually MOCK UP the results you want. Please do so - there is NO mock-up in the attachment. I also need to see the formula you tried to manipulate - I cannot troubleshoot without seeing what you did.
If it's just a case of flipping columns, then I am very surprised that you were unable to change the bits in red accordingly:
Please Login or Register to view this content.
Last edited by AliGW; 11-14-2023 at 09:40 AM.
I amended the data + reattached a new sheet, sorry for the confusion
Hi, the below worked thanks so much
LET(u,UNIQUE(C8:C15),
sa,SUMIF(C8:C15,u,B8:B15),
sb,SUMIF(F8:F16,u,G8:G16),VSTACK({"Identifier","Cost A","Cost B"},
HSTACK(u,sa,sb)))
how can I add a second criteria to the second part? please see cell F
Thank you
Yet again you have failed to manually mock up what you are looking for. I have no idea how you expect to incorporate the colours.
I'm not going to waste time trying to guess.
Provide a mock-up with annotations to explain properly what you are after.
Hi Ali, I'm sorry if I have confused you on this thread. In the attached, I have used your LET formula but I would like to add 1 more criteria to it.
I would like to to get the results in column L instead of column K by ignoring the ones in column E=Red
thanks
Thank you - at last a mock-up of what you want!
Try this:
Please Login or Register to view this content.
So I think you've seen this proposed solution - any feedback? Just a 'thanks' would do for starters - some acknowledgement of my continued efforts on your behalf ...
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
WOW it worked!, I just managed to look at it, you are a superstar thank you so so much.
You saved me a ton of time, brilliant
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Further to your PM:
LET allows us to create parameters:Please Login or Register to view this content.
u,UNIQUE(C8:C15) - u is the unique items from the range C8 to C15
sa,SUMIF(C8:C15,u,B8:B15) - sa is the sum of each of the unique items in the column B range
sb,SUMIFS(G8:G16,F8:F16,u,E8:E16,"<>Red") - sb is the sum of the unique items in column G where column E is not Red
Then we vertically stack the labels on top of the columns, which are brought together in the horizontal stack.
Hope this helps.
If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
much appreciated, learning everyday from you! thank you
Hi Ali, I would like to add one more criteria if possible.
I added 1 more column to the last file I shared with , column H.
I would like to to get the results in column L by ignoring the ones in column E=Red and H= 06/11/2023
Thanks
Not too sure about your expected results, but this?
Please Login or Register to view this content.
Perfect it did what I was looking for.
Thank you!
Great stuff.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks