Dyanamic YTD sum for multiple occurrences

[mmisogwebu]

Good day Experts

Kindly assist with the attached question
I want to add Mangoes sold from April to July
The answer should dynamically as i change the date in cell B1

Thank you in advance

 

[GraH - Guido]

If you have Microsoft365, you can try
=SUM(FILTER(DROP(C3:H6,,-(XMATCH(B1,C3:H3,,-1)-2)),B3:B6=C12))

 

[bosco_yip]

Another option for all Excel versions

=SUMPRODUCT(C4:INDEX(C7:H7,MATCH(B1,C3:H3,0))*(B4:B7=C12))

or

=SUMPRODUCT(OFFSET(C4,,,4,MATCH(B1,C3:H3,0))*(B4:B7=C12))

 

[p45cal]

Cross posted:

Dynamically sum YTD by month

[Solved] Dynamically sum YTD by month Excel

www.msofficeforums.com

 

[vletm]

mmisogwebu​

Please, reread Forum Rules - those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[mmisogwebu]

mmisogwebu​

Please, reread Forum Rules - those are for You too.

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

Thanks for the rules, i have posted for the first time and have taken note

 

[mmisogwebu]

Cross posted:

Dynamically sum YTD by month

[Solved] Dynamically sum YTD by month Excel

www.msofficeforums.com

Kindly note that i was posting for the first time and i was note aware of the rule

 

[vletm]

mmisogwebu​

Yes, we kindly not that before the 1st posting everyone would check some pages,
in this Forum those pages can find from
New Users - Please Start Here
Every Forum has same kind of pages
as well as everywhere else would be wise to learn - how/what to do?

 

[mmisogwebu]

If you have Microsoft365, you can try
=SUM(FILTER(DROP(C3:H6,,-(XMATCH(B1,C3:H3,,-1)-2)),B3:B6=C12))

View attachment 84502

Thank you very much , this has been resolved.

 

[mmisogwebu]

Another option for all Excel versions

=SUMPRODUCT(C4:INDEX(C7:H7,MATCH(B1,C3:H3,0))*(B4:B7=C12))

or

=SUMPRODUCT(OFFSET(C4,,,4,MATCH(B1,C3:H3,0))*(B4:B7=C12))

View attachment 84503

 

[mmisogwebu]

Thank you very much, this formula will resolve my query

 

[Peter Bartholomew]

This uses 365 with more of a programming mindset than that of writing a formula.

Given the slightest excuse, I would probably wrap the formula within a further LAMBDA function to allow an array of distinct products to be fed through the 'formula'.