How to Subtotal with Text Criteria

Update. I have attached the sample Excel file and added a little more detail to my question. Thanks again!

Apologies, I've searched the forums, but can't find the exact answer.

Here is the problem I have. I would like to subtotal values from Column C, when a filter is applied to either Column A or B or both. Right now, I am only able to calculate the total even when the filter is applied.

For example, if I filter Column A (Year) for "2023", then my expected result is E1 = $60,000 and E2 = $12,000 or if I filter Column B only (Expense) for "Travel", then E2 = $27,000.

Attachment 862910

Screenshot 2024-03-14 at 11.05.51 PM.png

Thank you in advance for your advice.

Welcome to the forum.

Is that a 365 version of Excel for Mac?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

One way (assuming 16.83 is an up to date version of O365), avoiding the use of the volatile OFFSET function:

=SUM(FILTER(C5:C8,(B5:B8=E1)*(MAP(A5:A8,LAMBDA(z,AGGREGATE(3,7,z)))=1)))

It didn't attach properly. Refer to Post 3.

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

It's a bit hard to follow what you want with such a tiny sample.... 10-20 rows is normally ideal, but try:

=ROWS(FILTER(C6:C11,(B6:B11=D1)*(MAP(A6:A11,LAMBDA(z,AGGREGATE(3,7,z)))=1)))

and set format as general.