SOLVED

Returning a sum based on multiple columns

Copper Contributor

HELP PLEASE. I am trying to return the sum of numbers in a column based on the first two columns. 

Three columns total. 

18 Replies
I think i was over thinking it. i have revised the spread sheet. i still need help
best response confirmed by HansVogelaar (MVP)
Solution

Hi @jasontindal 

 

Alternatively if you run Excel 2021 or 365 - no helper column:

Sample.png

 

in F3:

=SUM(
  FILTER(Table1[amount], (TEXT(Table1[column1],"MMMM") = F$2) * (Table1[type] = E3), 0)
)

 

OMG!!!!!!!!!!!!!!! THAT WORKED PERFECT. EVEN WHEN I DRUG IT DOWN TO NEXT CELL. YOU ARE A GENIOUS.... THANK YOU!!!!!!!!!@Lorenzo 

You're welcome. Glad to help
There's a Mark as response link at the bottom of each post you get here. It helps people who Search for existing solutions - Thanks in advance

@jasontindal 

 

Just in case... A PivotTable does what you expect auto. (no formula):

Sample.png

OK, I thought I could just replicate that to the final sheet I needed it for now that I have it built. Will you please do the same thing on this one for the month and year? there are some notes on first page as well. ty very much!!!!!!@Lorenzo 

Hi @jasontindal 

 

CAN YOU MAKE THIS TABLE UPDATE THE DROP DOWN BOXES AS I INPUT DATA. RIGHT NOW I HAVE TO DRAG EVERYTHING FOR THE TABLES TO WORK

 

Really unclear to me as I didn't find DROP DOWN BOXES so did what I understood...

 

#1 In sheet 'PULL DATA FROM' you'll currently see:

Sample.png

Don't write anything below these 2 values. A31 & B31 contain formulas that will respectively Spill in A32, A33... and B32, B33... as you enter data in the TRANSACTIONS table. So, when you will enter transactions for say Feb 2023 you will see:

Sample2.png

 

#2 In sheet 'TRANSACTIONS' the YearMonth drop-down lists refer to the dynamic ranges created above (#1). So logically, with your current Transactions, they respectively only show January & 2024 for now

 

(there were a bunch of #Ref errors in the Name Manager. I deleted them)

THANK YOU. I do not think the correct file was uploaded.
1. on the 'month overview' page, when entering the month from drop-down box C6 and year C7, it will return the sum in C9 and C10 respectively. maybe how c6 and c7 look needs to be modified. this type of formula and sheet is out of my wheelhouse. I just know the end result. lol.

2. on the 'transactions' page, why do the drop-down boxes not automatically come down (without dragging the corner) when you start typing on the next line?

 

there should be a file uploaded for today. with 2-11-2024 in name

@Lorenzo 

@jasontindal 

 

I do not think the correct file was uploaded Thank you for the waste of time

 

#1 On the 'MONTH OVER VIEW-MAIN PAGE' sheet, should the Category, SubCat & Amounts be based on twhat's selected re. MONTH & YEAR?

Sample.png

 

#2 On the 'Transactions' page, why do the drop-down boxes not automatically come down (without dragging the corner) when you start typing on the next line? Probably due to this:

Untitled.png

(something I'm not gonna fix on your behalf sorry - your application)

 

#3 Do you run Excel 2021 or 365?

#1. The amounts should be based on the transactions sheet. ty. @Lorenzo 

@jasontindal 

What about #3 Do you run Excel 2021 or 365?

I am using 365. Ty@Lorenzo 

@jasontindal 

 

I am using 365 OK, so see attached file

 

NB:

  • I had to re-create sheet 'MONTH OVER VIEW-MAIN PAGE' due to strange things happening with it (previous sheet renamed as 'DELETE THIS SHEET')
  • Took the freedom to change/rename a few things for clarity

 

With the exception of formulas in C9:C11 all others are dynamic arrays that Spill on the sheet. As you play with the drop down lists and/or make changes to the Transactions table you'll immediately see the result

 

Formulas for INCOME, EXPENSE & SUM OF ALL CATEGORIES are LAMBDAs stored in the Name Manager (I deleted all names that had #REF error):

  • for SUM OF ALL CATEGORIES it's named SumAllByCategory
  • for INCOME & EXPENSE it's named SelectedPeriodSubCats

Also Named C6 as SelectedMonth and C7 as SelectedYear

Thank you and I do believe that is exactly the end result I wanted. I cannot do any modifications, I.E., color cells, border, etc...). I appears to be locked. How do I modify that sheet?
OK, I figured the sheet, just had to unprotect it.
How do I add months (only lets me select January) and add more years for future use? ty.

@jasontindal 

 

How do I add months (only lets me select January) and add more years for future use?

I explained this earlier... Look at sheet PULL DATA FROM, cells A31, B31. They currently only "say" JANUARY & 2024 as you currently only have Transactions in that period:

Sample.png

 

  • Go the the TRANSACTIONS sheet and add a dummy record in say March 2023
  • Go back to the PULL DATA FROM and you will see MARCH in the Months and 2023 in the Years
  • Go to the MONTH OVER VIEW-MAIN PAGE, the drop-down lists now offer you MARCH & 2023

Ah, ok, ty. Thank you for your help as this is the first online forum I have ever used. That is pretty awesome how you did that. On a scale of 1-10, my overall knowledge of Excel is probably a 2-3 of all there is to know, so I appreciate the knowledge of experienced people like you.!!!@Lorenzo 

You're @jasontindal & Thanks for providing feedback

1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

Hi @jasontindal 

 

Alternatively if you run Excel 2021 or 365 - no helper column:

Sample.png

 

in F3:

=SUM(
  FILTER(Table1[amount], (TEXT(Table1[column1],"MMMM") = F$2) * (Table1[type] = E3), 0)
)

 

View solution in original post