SOLVED

Beginner needs help with a nested formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1101568%22%20slang%3D%22en-US%22%3EBeginner%20needs%20help%20with%20a%20nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101568%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20fairly%20new%20to%20Excel%20and%20have%20been%20trying%20to%20solve%20this%20issue%20for%20almost%20a%20day%20now.%20I%20am%20trying%20to%20'automate'%20a%20part%20of%20my%20budget%20excel%20sheet%20through%20the%20use%20of%20an%20IF%20statement%20that%20includes%20a%20nested%20formula.%20I%20have%20added%20a%20small%20example%20of%20my%20excel%20sheet%20to%20the%20appendix%20of%20this%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20excel%20sheet%20has%20two%20tabs%3B%20dashboard%20and%20transactions.%20I%20need%20the%20sheet%20to%20do%20the%20following.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F164735iFA6009596517766F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Help%20with%20excel%20sheet%20overview.png%22%20title%3D%22Help%20with%20excel%20sheet%20overview.png%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EInstructions%20overview%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I've%20got%20the%20following%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DALS(B15%3DABN_Amro_Transacties_Januari%5BMaand%5D%3BSOM.ALS(ABN_Amro_Transacties_Januari%5B%5B%23Alles%5D%3B%5BCategorie%5D%5D%3B%22Boodschappen%22%3BABN_Amro_Transacties_Januari%5BBedrag%5D)%3B%22Error%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20it%20checks%20if%20the%20Month%20in%20Column%20on%20a%20different%20tab%20matches%20the%20month%20at%20B15%3C%2FP%3E%3CP%3EIf%20it%20does%2C%20the%20following%20SOM.ALS%20(SUM.IF)%20checks%20if%20the%20category%20column%20contains%20a%20certain%20word%2C%20in%20this%20case%20it's%20'Boodschappen'%20(Groceries).%20If%20it%20does%2C%20the%20formula%20should%20grab%20the%20value%20in%20the%20'Bedrag'%20column%20and%20add%20it%20to%20the%20total%20value%20of%20the%20category.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20you%20understand.%20I%20would%20appreciate%20the%20help%20greatly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJason%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1101568%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102601%22%20slang%3D%22en-US%22%3ERe%3A%20Beginner%20needs%20help%20with%20a%20nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102601%22%20slang%3D%22en-US%22%3E%3CP%3EAnyone%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1102714%22%20slang%3D%22en-US%22%3ERe%3A%20Beginner%20needs%20help%20with%20a%20nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1102714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F513554%22%20target%3D%22_blank%22%3E%40JasonNL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20build%20a%20simple%20pivot%20table%3F%3C%2FP%3E%3CP%3EMaand%20in%20columns%20area.%3C%2FP%3E%3CP%3ESubcategorie%20in%20rows%20area.%3C%2FP%3E%3CP%3EBedrag%20in%20values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

 

I fairly new to Excel and have been trying to solve this issue for almost a day now. I am trying to 'automate' a part of my budget excel sheet through the use of an IF statement that includes a nested formula. I have added a small example of my excel sheet to the appendix of this post.

 

The excel sheet has two tabs; dashboard and transactions. I need the sheet to do the following. Instructions overviewInstructions overview

 

So far I've got the following formula. 

=ALS(B15=ABN_Amro_Transacties_Januari[Maand];SOM.ALS(ABN_Amro_Transacties_Januari[[#Alles];[Categorie]];"Boodschappen";ABN_Amro_Transacties_Januari[Bedrag]);"Error")

 

First it checks if the Month in Column on a different tab matches the month at B15

If it does, the following SOM.ALS (SUM.IF) checks if the category column contains a certain word, in this case it's 'Boodschappen' (Groceries). If it does, the formula should grab the value in the 'Bedrag' column and add it to the total value of the category. 

 

Hopefully you understand. I would appreciate the help greatly.

 

Kind regards,

 

Jason

 

 

 

2 Replies
Highlighted

Anyone? 

Highlighted
Solution

@JasonNL 

Why not build a simple pivot table?

Maand in columns area.

Subcategorie in rows area.

Bedrag in values area.