Hello Members,
I am using excel 2016 and have some data (please see attachment). Trying to use SUMIFS function to find 'Amount' of last sold item.
I'm stuck on how to put recent date as criteria in function.
Can you please help on how to achieve this.
Thanks
Last edited by laxminarayana; 09-04-2022 at 03:41 AM.
No attachment.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Sorry. I missed. Now included in original post.
Last edited by laxminarayana; 09-03-2022 at 07:14 PM.
I see a picture, but not an attachment. Please read the yellow banner at the top of the page.
My bad. Attached sample in excel format.
I have tried below formula, but something seems to be wrong.
=SUMIFS(D2:D10,A2:A10,"Capsicum",B2:B10,"Yellow",C2:C10,MAX(C2:C10))
Last edited by laxminarayana; 09-04-2022 at 03:51 AM.
make a table of the data => insert => table.
sort the data on column C (A-Z)
F2 =A2&B2
E2=IF(COUNTIF(F$2:$F$10,$F2)=COUNTIF($F2:$F2,F2),"yes","")
filter on column E with the value yes
See the attached file.
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.
Then please update your forum profile, which tells us you are using Excel 2007.I am using excel 2016
Not clear to me.
Maybe this???
=MAX(IF(A2:A10="Capsicum",IF(B2:B10="Yellow",C2:C10)))
Or this???
=MAX(IF(A2:A10="Capsicum",IF(B2:B10="Yellow",D2:D10)))
Enter with CTRL+SHIFT+ENTER.
Last edited by AliGW; 09-04-2022 at 06:47 AM. Reason: Typo corrected.
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.
@Ali: suspect the second one should be:Formula:
Please Login or Register to view this content.
Yes - correct. Typo on my part - will update. Thanks.
@Ali: plus some brackets, I think
Amount =AGGREGATE(14,6,D2:D10/(A2:A10=A3)/(B2:B10=B3)/(C2:C10=AGGREGATE(14,6,C2:C10/(A2:A10=A3)/(B2:B10=B3),1)),1)
Thank you very much. This helped me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks