SOLVED

Help with Summing an Array based on given Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2363111%22%20slang%3D%22en-US%22%3EHelp%20with%20Summing%20an%20Array%20based%20on%20given%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2363111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EDevice%20%26amp%3B%20OS%20Platform%3A%3C%2FSTRONG%3E%20PC%2FWindows%2010%3C%2FP%3E%3CP%3E%3CSTRONG%3EExcel%20product%20name%20and%20version%20number%3A%26nbsp%3B%3C!--%20%20%20%20%20StartFragment%20%20%20%20%20%20--%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%3CSPAN%20class%3D%22cf0%22%3EMicrosoft%20Excel%20for%20Microsoft%20365%20MSO%20(16.0.13929.20360)%2064-bit%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22pf0%22%3E%3CSPAN%20class%3D%22cf0%22%3E%3CSTRONG%3EExcel%20File%3A%3C%2FSTRONG%3E%20%22CASH%20FLOW%20MONITOR.xlsm%22%20attached%20(Included%20Data%20is%20non-descript)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3C!--%20%20%20%20%20EndFragment%20%20%20%20%20%20--%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20life%20of%20me%2C%20I%20cannot%20get%20a%20formula%20to%20work%20(and%20I'm%20supposed%20to%20be%20good%20at%20Excel)!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20populate%20the%20Cells%20F7%3AQ33%20on%20Sheet%20SUMMARY%20with%20Totals%20from%20the%20Range%20REQUIRED_DATA%20that%20meet%20certain%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Array%20with%20the%20following%20Columns%3A%20DATE%26nbsp%3B%20%7C%26nbsp%3B%20TRANSACTION_DESCRIPTION%26nbsp%3B%20%7C%26nbsp%3B%20CATEGORY%26nbsp%3B%20%7C%26nbsp%3B%20AMOUNT%20called%20REQUIRED_DATA%20on%20Sheet%20%22RAW%20CSV%20DATA%22.%3C%2FP%3E%3CP%3EEach%20required%20Column%20for%20calculation%20DATE%2C%20CATEGORY%20and%20AMOUNT%20in%20the%20Array%20also%20has%20its%20own%20Range%20Name%20and%20are%20the%20same%20length.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20simply%20want%20to%20do%20the%20following%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECheck%20the%20DATE%20Range%20and%20SUM()%20each%20AMOUNT%20that%20meets%20a%20particular%20CATEGORY%20for%20each%20Month%20as%20follows%3A%3C%2FP%3E%3CP%3EMONTH%20%3D%20Cells%20in%20Range%20DATE%20%3D%20January%2C%20February%2C%20etc...%20(Dates%20are%20formatted%20as%20%22dd%2Fmmmm%2Fyy%22)%3C%2FP%3E%3CP%3ECATEGORY%20%3D%20Cells%20in%20the%20Range%20ACTIVE_CATEGORIES%20on%20Sheet%20%22SUMMARY%22%2C%20and%20sum%26nbsp%3Bthe%20Range%20AMOUNT%20that%20meets%20the%20criteria%20of%20MONTH%20and%20CATEGORY.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20SUMIFS()%20and%20even%20tried%20the%20Array%20Formula%20%7B%3DSUM((MONTH(DATE)%3D1)*(CATEGORY%3DB7)*(AMOUNT))%7D%20which%20should%20work%20but%20it%20doesn't.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20help%2C%20I've%20spent%20days%20on%20this%20and%20have%20never%20had%20this%20issue%20before.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2363111%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2363151%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Summing%20an%20Array%20based%20on%20given%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2363151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056490%22%20target%3D%22_blank%22%3E%40Tony_Imbruglia%3C%2FA%3E%26nbsp%3BSince%20you%20have%20access%20all%20the%20newest%20dynamic%20array%20functions%2C%20why%20not%20use%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20revised%20your%20schedule%20so%20that%20it%20uses%20FILTER.%20See%20if%20this%20would%20indeed%20work%20for%20you.%20And%2C%20your%20SUMMARY%20sheet%20had%20a%20hidden%20column%20that%20listed%20the%20unique%20categories.%20Then%20in%20B%20you%20sorted%20that%20unique%20listing.%20Note%20that%20you%20can%20combine%20the%20two%20into%20one%20single%20formula%20like%20%3CSTRONG%3E%3DSORT(UNIQUE(%3CRANGE%3E)%3C%2FRANGE%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESo%20I%20did%20that%20and%20got%20rid%20of%20column%20A.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Device & OS Platform: PC/Windows 10

Excel product name and version number: 

Microsoft Excel for Microsoft 365 MSO (16.0.13929.20360) 64-bit

Excel File: "CASH FLOW MONITOR.xlsm" attached (Included Data is non-descript)

 

For the life of me, I cannot get a formula to work (and I'm supposed to be good at Excel)!

 

I want to populate the Cells F7:Q33 on Sheet SUMMARY with Totals from the Range REQUIRED_DATA that meet certain criteria.

 

I have an Array with the following Columns: DATE  |  TRANSACTION_DESCRIPTION  |  CATEGORY  |  AMOUNT called REQUIRED_DATA on Sheet "RAW CSV DATA".

Each required Column for calculation DATE, CATEGORY and AMOUNT in the Array also has its own Range Name and are the same length.

 

I simply want to do the following:

Check the DATE Range and SUM() each AMOUNT that meets a particular CATEGORY for each Month as follows:

MONTH = Cells in Range DATE = January, February, etc... (Dates are formatted as "dd/mmmm/yy")

CATEGORY = Cells in the Range ACTIVE_CATEGORIES on Sheet "SUMMARY", and sum the Range AMOUNT that meets the criteria of MONTH and CATEGORY.

 

I have tried SUMIFS() and even tried the Array Formula {=SUM((MONTH(DATE)=1)*(CATEGORY=B7)*(AMOUNT))} which should work but it doesn't.

 

Can you please help, I've spent days on this and have never had this issue before.

3 Replies
best response confirmed by Tony_Imbruglia (New Contributor)
Solution

@Tony_Imbruglia Since you have access all the newest dynamic array functions, why not use them.

 

I have revised your schedule so that it uses FILTER. See if this would indeed work for you. And, your SUMMARY sheet had a hidden column that listed the unique categories. Then in B you sorted that unique listing. Note that you can combine the two into one single formula like =SORT(UNIQUE(<range>)

So I did that and got rid of column A. See attached.

 

Your a Genius Riny That is perfect. I didn't know about FILTER. thank you so much for also amending the Workbook for me. I'm so happy.

@Tony_Imbruglia Glad I could help.

 

A few more tips, if I may.

Get rid of merged cells. They are just a pain in the a.... Use "Format Cells, Alignment, Horizontal, Center Across Selection" in stead. Get rid of empty columns in the raw data sheet.

 

When I updated the formulae and pressed enter, I noticed that the schedule calculated very slow. And you don't even have that much data in it. Consider using Power Query (PQ) to connect to, clean, sort, group and transform the raw data. Once you have done that, you can get rid of VLOOKUP and its associated (mostly redundant) tables, and all (or most of) the nested IF, TEXTJOIN, INDIRECT and CONCAT statements in your sheet. and you don't need to "reserve" 5000 rows with all these formulae in it. PQ is much more powerful and flexible in that respect. And it will most likely speed up your sheet.