Simple Problem, Tricky Solution

Craig Peter DG

Craig Peter DG

New Member
Joined
Sep 10, 2016
Messages
33
Office Version
  1. 2013
Hi

My simple problem is that I need to Sum Values in a Table array within a given date range, with a unique identifying criteria. There are about 15 unique criterias and 365 days of separate dates for a years worth of data.

I need Totals by Month for each of the 15 unique identifiers......

This table shows where I want my results to go. These are Expense types.

Problem is, the values in the source data table are date by date by date, and I need those summarised for each Expense type into month by month, so, grouped, as it were. In addition, the expense categories you see in this table below, well in the source data, they run across the page in columns, not down the page in rows as you see here.


Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14
Operating Expenses
Jul-23​
Aug-23​
Sep-23​
Oct-23​
Nov-23​
Dec-23​
Jan-24​
Feb-24​
Mar-24​
Apr-24​
May-24​
Jun-24​
FY TOTAL
BANK/MERCHANT FEESSUM ALL VALUES FOR JULY FOR THIS EXPENSE TYPE$ -
BOOKEEPING SERVICES$ -
COMPLIANCE/REGULATORY$ -
CONSUMABLES (STUDIO SUPPLIES)$ -
DIGITAL SERVICES/WEB HOSTING$ -
EDUCATION/TRAINING$ -
HOME OFFICE SUPPLIES$ -
INSURANCE (INCOME PROTECT)$ -
LAUNDRY$ -
PHONE/INTERNET$ -
POS$ -
POSTAGE/FREIGHT$ -
PPE$ -
PROMO/MARKETING$ -
RENT (COMMERCIAL$ -
REPAIRS/MAINT$ -
SOFTWARE/HARDWARE$ -
SUBS/MEMBERSHIPS$ -
TOOLS & EQUIP$ -
TRAVEL/ACCOM/MEALS$ -
UTILITIES$ -
VEHICLE$ -
Capital Purchases$ -
COMPUTER/TABLET$ -
MACHINE/EQUIP$ -

So i have a Result table with a different layout to the source data table, I need to sum values that appear down columns in the source data, with different date formattting.

Help??????????? Perhaps an Index Match with an embedded Sumifs????

I dont know.

Im using Excel 2013.

Thankyou

Here is a snapshot of the Source data. See how the Expense categories in columns.



Monthly Expenditure - By Expense Category 2023-2024
OPERATING EXPENSESCAPITAL PURCHASES
DateTotal (Incl GSTVEHICLEREPAIRS/MAINTRENT (COMMERCIALPROMO/MARKETINGCONSUMABLES (STUDIO SUPPLIES)HOME OFFICE SUPPLIESPOSDIGITAL SERVICES/WEB HOSTINGPOSTAGE/FREIGHTTOOLS & EQUIPTRAVEL/ACCOM/MEALSPHONE/INTERNETUTILITIESSUBS/MEMBERSHIPSINSURANCE (INCOME PROTECT)EDUCATION/TRAININGBOOKEEPING SERVICESSOFTWARE/HARDWAREBANK/MERCHANT FEESLAUNDRYCOMPLIANCE/REGULATORYPPECOMPUTER/TABLETMACHINE/EQUIP
1/07/2023​
$ 192.90$ 192.90
1/07/2023​
$ 131.05$ 131.05
1/07/2023​
$ 4.85$ 4.85
1/07/2023​
$ 25.00$ 25.00
1/07/2023​
$ 123.20$ 123.20
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Perhaps SUMPRODUCT:
Book1
ABCDEFGHIJKLMN
1Operating ExpensesJul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-24FY TOTAL
2BANK/MERCHANT FEES0000000000000
3BOOKEEPING SERVICES623.20623.20000000001246.4
4COMPLIANCE/REGULATORY0000000000000
5CONSUMABLES (STUDIO SUPPLIES)0000000000000
6DIGITAL SERVICES/WEB HOSTING0000000000000
7EDUCATION/TRAINING0000000000000
8HOME OFFICE SUPPLIES0000000000000
9INSURANCE (INCOME PROTECT)0000000000000
10LAUNDRY0000000000000
11PHONE/INTERNET0000000000000
12POS2502500000000050
13POSTAGE/FREIGHT0000000000000
14PPE99909990000000001998
15PROMO/MARKETING0000000000000
16RENT (COMMERCIAL0000000000000
17REPAIRS/MAINT328.80328.8000000000657.6
18SOFTWARE/HARDWARE0000000000000
19SUBS/MEMBERSHIPS0000000000000
20TOOLS & EQUIP1000100000000000200
21TRAVEL/ACCOM/MEALS0000000000000
22UTILITIES4000400000000000800
23VEHICLE0000000000000
24Capital Purchases0000000000000
25COMPUTER/TABLET0000000000000
26MACHINE/EQUIP0000000000000
Sheet1
Cell Formulas
RangeFormula
B2:M26B2=SUMPRODUCT(--(MONTH(Sheet2!$A$3:$A$12)=MONTH(B$1))*--(Sheet2!$B$2:$Z$2=$A2),Sheet2!$B$3:$Z$12)
N2:N26N2=SUM(B2:M2)


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1OPERATING EXPENSESCAPITAL PURCHASES
2DateTotal (Incl GSTVEHICLEREPAIRS/MAINTRENT (COMMERCIALPROMO/MARKETINGCONSUMABLES (STUDIO SUPPLIES)HOME OFFICE SUPPLIESPOSDIGITAL SERVICES/WEB HOSTINGPOSTAGE/FREIGHTTOOLS & EQUIPTRAVEL/ACCOM/MEALSPHONE/INTERNETUTILITIESSUBS/MEMBERSHIPSINSURANCE (INCOME PROTECT)EDUCATION/TRAININGBOOKEEPING SERVICESSOFTWARE/HARDWAREBANK/MERCHANT FEESLAUNDRYCOMPLIANCE/REGULATORYPPECOMPUTER/TABLETMACHINE/EQUIP
301/07/2023192.9192.9
401/07/2023131.05131.05100400999
501/07/20234.854.85500
601/07/20232525
701/07/2023123.2123.2
823/09/2023192.9192.9
923/09/2023131.05131.05100400999
1023/09/20234.854.85500
1123/09/20232525
1223/09/2023123.2123.2
Sheet2
 
Upvote 0
Georgiboy said:
Perhaps SUMPRODUCT:
Book1
ABCDEFGHIJKLMN
1Operating ExpensesJul-23Aug-23Sep-23Oct-23Nov-23Dec-23Jan-24Feb-24Mar-24Apr-24May-24Jun-24FY TOTAL
2BANK/MERCHANT FEES0000000000000
3BOOKEEPING SERVICES623.20623.20000000001246.4
4COMPLIANCE/REGULATORY0000000000000
5CONSUMABLES (STUDIO SUPPLIES)0000000000000
6DIGITAL SERVICES/WEB HOSTING0000000000000
7EDUCATION/TRAINING0000000000000
8HOME OFFICE SUPPLIES0000000000000
9INSURANCE (INCOME PROTECT)0000000000000
10LAUNDRY0000000000000
11PHONE/INTERNET0000000000000
12POS2502500000000050
13POSTAGE/FREIGHT0000000000000
14PPE99909990000000001998
15PROMO/MARKETING0000000000000
16RENT (COMMERCIAL0000000000000
17REPAIRS/MAINT328.80328.8000000000657.6
18SOFTWARE/HARDWARE0000000000000
19SUBS/MEMBERSHIPS0000000000000
20TOOLS & EQUIP1000100000000000200
21TRAVEL/ACCOM/MEALS0000000000000
22UTILITIES4000400000000000800
23VEHICLE0000000000000
24Capital Purchases0000000000000
25COMPUTER/TABLET0000000000000
26MACHINE/EQUIP0000000000000
Sheet1
Cell Formulas
RangeFormula
B2:M26B2=SUMPRODUCT(--(MONTH(Sheet2!$A$3:$A$12)=MONTH(B$1))*--(Sheet2!$B$2:$Z$2=$A2),Sheet2!$B$3:$Z$12)
N2:N26N2=SUM(B2:M2)


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1OPERATING EXPENSESCAPITAL PURCHASES
2DateTotal (Incl GSTVEHICLEREPAIRS/MAINTRENT (COMMERCIALPROMO/MARKETINGCONSUMABLES (STUDIO SUPPLIES)HOME OFFICE SUPPLIESPOSDIGITAL SERVICES/WEB HOSTINGPOSTAGE/FREIGHTTOOLS & EQUIPTRAVEL/ACCOM/MEALSPHONE/INTERNETUTILITIESSUBS/MEMBERSHIPSINSURANCE (INCOME PROTECT)EDUCATION/TRAININGBOOKEEPING SERVICESSOFTWARE/HARDWAREBANK/MERCHANT FEESLAUNDRYCOMPLIANCE/REGULATORYPPECOMPUTER/TABLETMACHINE/EQUIP
301/07/2023192.9192.9
401/07/2023131.05131.05100400999
501/07/20234.854.85500
601/07/20232525
701/07/2023123.2123.2
823/09/2023192.9192.9
923/09/2023131.05131.05100400999
1023/09/20234.854.85500
1123/09/20232525
1223/09/2023123.2123.2
Sheet2
Click to expand...
Thank you Georgieboy. I was not aware SunProduct could be used in a problem where you are NOT multiplying data? I thought you could only use SumProduct if you are multiplying two values.
 
Upvote 0
Craig Peter DG said:
Thank you Georgieboy. I was not aware SunProduct could be used in a problem where you are NOT multiplying data? I thought you could only use SumProduct if you are multiplying two values.
Click to expand...
You are multiplying data. But usually it's multiplying the value(s) you want to sum by the 1/0 (TRUE/FALSE) results of the conditions, then adding the results of those multiplications.
 
Upvote 0
dreid1011 said:
You are multiplying data. But usually it's multiplying the value(s) you want to sum by the 1/0 (TRUE/FALSE) results of the conditions, then adding the results of those multiplications.
Click to expand...
Ah i see. Yes i get it. All the daily transactional values for each expense type are in the.down column for each day. I want to add these then group them into a monthly total for each Expense type. So i can multiply by 1 then sum them......yes?
 
Upvote 0
Craig Peter DG said:
Ah i see. Yes i get it. All the daily transactional values for each expense type are in the.down column for each day. I want to add these then group them into a monthly total for each Expense type. So i can multiply by 1 then sum them......yes?
Click to expand...
More or less, in this situation the conditional check is matching the dates, so a match would return 1 and a non-match would return 0. Then the data value for each line would be multiplied by the 1 or 0 returned by the check. Once all the rows are checked for their dates, and the values multiplied by 1 or 0, the results of the multiplications are summed.

For example, looking up dates in January:

DateValueConditional check in SUMPRODUCTMultiplication Result
1/1/24500Date in January? TRUE or 11*500 = 500
1/15/241000Date in January? TRUE or 11*1000 = 1000
2/10/24650Date in January? FALSE or 00*650 = 0

The total summed result for dates in January would be 1500.
 
Upvote 0
dreid1011 said:
You are multiplying data. But usually it's multiplying the value(s) you want to sum by the 1/0 (TRUE/FALSE) results of the conditions, then adding the results of those multiplications.
Click to expand...
Thankyou for the link to XL2BB. I dont see it in my Add-ins list because I am only using Excel 2013 Version. Bu thankyou anyway.
 
Upvote 0
Craig Peter DG said:
Thank you Georgieboy. I was not aware SunProduct could be used in a problem where you are NOT multiplying data? I thought you could only use SumProduct if you are multiplying two values.
Click to expand...

Hi Georgieboy

I tried the Sumproduct formula you suggested. In the first cell reference under Jul 23 I got a Zero result which is TRUE. There were no Expense values for Bank Merchant Fees in Jul 23. However, when I copied the formula to the remaining range in my results table, I got VALUE and NA errors. I double checked the formula a few times. This is the formula i transferred from your assistance to my own Worksheet:-

=SUMPRODUCT(--MONTH($A$5:A174)=MONTH(B$182)*--($C$4:$Z$4=$A183),$C$5:$Z$174)

Sorry to ask you, but can you able to see where I went wrong please????

Regards

Craig


Column1Column2Column3Column4Column5Column6Column7Column8
Operating Expenses
Jul-23​
Aug-23​
Sep-23​
Oct-23​
Nov-23​
Dec-23​
Jan-24​
BANK/MERCHANT FEES
0​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
BOOKEEPING SERVICES
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
COMPLIANCE/REGULATORY
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
CONSUMABLES (STUDIO SUPPLIES)
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
DIGITAL SERVICES/WEB HOSTING
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
EDUCATION/TRAINING
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
HOME OFFICE SUPPLIES
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
INSURANCE (INCOME PROTECT)
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
LAUNDRY
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
PHONE/INTERNET
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
POS
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
POSTAGE/FREIGHT
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
PPE
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
PROMO/MARKETING
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
RENT (COMMERCIAL
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
REPAIRS/MAINT
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
SOFTWARE/HARDWARE
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
SUBS/MEMBERSHIPS
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
TOOLS & EQUIP
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
TRAVEL/ACCOM/MEALS
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
UTILITIES
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
VEHICLE
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
Capital Purchases
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
COMPUTER/TABLET
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
MACHINE/EQUIP
#VALUE!​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
 
Upvote 0
This part: A174 has no $, it should be $A$174

Also, you have no sheet references in your formula, is all of your data on one sheet?
 
Upvote 0
Georgiboy said:
This part: A174 has no $, it should be $A$174

Also, you have no sheet references in your formula, is all of your data on one sheet?
Click to expand...
Yep. All my data is in same worksheet. Thats why no Sheet references in the formula. Hey, why do I have to Lock A174 please?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Craig Peter DG
  • Question Question
SUM OF DAILY TRANSACTION VALUES INTO MONTHLY SUMMARY - MULTIPLE CRITERIA
Replies
3
Views
335
Georgiboy
Georgiboy
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
127
Fluff
Fluff
W
  • Solved
Fiscal Quarter Percentage difference
2 3
Replies
22
Views
1K
Walker_Ice
W
Sufiyan97
  • Question Question
Partial Text Lookup
2
Replies
18
Views
2K
Sufiyan97
Sufiyan97
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top