Help with Sumproduct or similar formula to total items found by Sumproduct for invoices paid in a month.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have got the following formula in cells B2 to B7 and D2 to D7 in sheet called Invoices

Rich (BB code):
=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2))

A2 currently has the value January 2021 but it can hold values of February 2021, March 2021….

It basically looks at range G11 to G40 and should it find January 2021, then it updates B2.

Therefore should it find 3 dates which fall in January 2021 in the range it updates B2 to 3.

What I would like to happen in E2:G2 is when it finds data that meets my criteria in G11 to G40, I would like it to sum the data in corresponding cells in K11:K40 and put the answer in E2, sum the data in corresponding cells in L11:L40 and put the answer in F2 and sum the data in corresponding cells in M11:M40 and put the answer in G2.

Therefore if the existing SUMPRODUCT find data matching my criteria in cells G11:G13, then E2 should be sum of cells K11:K13, F2 should be sum of the cells in L11:L13 and G2 should be the sum of the cells in M11:M13.

I need to redesign the sheet to allow the above summing to be reported for all other months, which I will do once I have got the above working.

I will be grateful for any assistance/suggestions offered.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
For E2 try:

Book2
ADEFGK
1
2January 20218
3
10
111/2/20211
121/8/20212
132/6/20213
143/4/20214
151/21/20215
162/10/20216
Sheet1
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2),K11:K40)
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
AhoyNC,

Firstly, Thank you for your time and effort.

I have just tried it, and BINGO, works as expected.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

AhoyNC,

I wonder if you can help with my post #1 and your solution detailed in #2

I initially had the formula
Rich (BB code):
=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2))
in sheet called Invoices as it has the required data and it used to work.

I have now decided to create a new sheet called Invoice Payment Details and move the formula in there.

The formula now looks like
Rich (BB code):
=SUMPRODUCT(--(TEXT(Invoices!F$11:F$299,"mmmm yyyy")=Formula!C2))

Where Formula!C2 has January 2021 and is made up using
Rich (BB code):
=CONCATENATE(B2," ",$A$1)
, where B2 is January and A1 is 2021

The data in F11 to F299 is formatted as dd/mm/yy in sheet called Invoices

The formula in sheet Invoice Payment Details returns #N/A.

I also had
Rich (BB code):
=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2),K11:K40)
in sheet called Invoices, and have again moved it to the new sheet called Invoice Payment Details. The formula now look like
Rich (BB code):
=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)

And that again returns #N/A

Can you please help to let me where I am going wrong?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
Your formula works for me.

Formula Sheet
Book2
ABC
12021
2JanuaryJanuary 2021
38
Formula
Cell Formulas
RangeFormula
C2C2=CONCATENATE(B2," ",$A$1)
C3C3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)


Invoices Sheet
Book2
GHIJK
10DateAmount
111/2/20211
121/8/20212
132/6/20213
143/4/20214
151/21/20215
162/10/20216
Invoices
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

AhoyNC,

I am away from my.PC at the moment.

I will have a look @ your response tomorrow and come back to you.

Thanks for the time you have given to look @ this, I appreciate it.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
AhoyNC,

I have had a play and so far I cannot get the results that I require.no joy.

Using Xl2bb, I attach sheets called Invoice Payment Details, Invoices and Formula all of which exist in the same workbook.
I the Invoices sheet, I have put the column numbers to aid you.

In Invoice Payment Details

I Invoice Payment Details sheet, Column K onwards are the values I am expecting for January thru March.

Columns B and C are formatted as Number, Columns D to E are formatted as Currency.

In B3, I would expect a value of 3 as there are 3 Due Dates which fall in January 2021 from sheet called Invoices (Column F). (Rows, 11 - 13)

In C3, I would expect a value of 2 as there are 2 Paid Invoices which fall in January 2021 from sheet called Invoices (Column G). (Rows, 11, 12)

In D3, I would expect a value of £125.00 as indicated on sheet called Invoices (Column K). ROW 11

In E3, I would expect a value of £125.00 as paid by Cheque as indicated on sheet called Invoices (Column L). ROW 12

In F3, I would expect a value of £0.00 as no one has paid via BACS indicated on sheet called Invoices (Column M).


In B4, I would expect a value of 4 as there are 4 Due Dates which fall in February 2021 from sheet called Invoices (Column F). (Rows, 14, 17 - 19)

In C4, I would expect a value of 3 as there are 3 Paid Invoices which fall in February 2021 from sheet called Invoices (Column G). (Rows, 14, 18 - 19)

In D4, I would expect a value of £1900.00 as paid by Cash as indicated on sheet called Invoices (Column K). ROW 14 and Row 18

In E4, I would expect a value of £0.00 as no one has paid by Cheque as indicated on sheet called Invoices.

In F4, I would expect a value of £90.00 as no one has paid by BACS as indicated on sheet called Invoices.


In B5, I would expect a value of 4 as there are 4 Due Dates which fall in March 2021 from sheet called Invoices (Column F). (Rows, 15, 21 - 23)

In C5, I would expect a value of 5 as there are 5 Paid Invoices which fall in March 2021 from sheet called Invoices (Column G). (Rows, 13, 20 - 23)

In D5, I would expect a value of £200.00 as paid by Cash as indicated on sheet called Invoices (Column K). ROW 14

In E5, I would expect a value of £300.00 as paid by Cheque as indicated on sheet called Invoices.

In F5, I would expect a value of £2400.00 as paid by BACS as indicated on sheet called Invoices.

I also attach sheet called Invoice Issued 2021 for completeness.

2021 Yearly Club Accounts.xlsm
ABCDEFGHIJKLMNOPQR
1Monthly Invoice payments for 2021
2MonthInvoices Due for payment in the monthInvoices paid in the monthCashChequesBACSTotalsInvoices Due for payment in the monthInvoices Due for payment in the month Row NumbersInvoices paid in the monthInvoices paid in the month Row NumbersCashChequesBACSTotals
3January#N/A#N/A#N/A#N/A#N/A#N/A3 11 -132 11 - 12£125.00£125.00£0.00£250.00
4February#N/A#N/A#N/A#N/A#N/A#N/A4 14, 17 - 193 14, 18 - 19£1,900.00£0.00£90.00£1,990.00
5March#N/A#N/A#N/A#N/A#N/A#N/A4 15, 21 - 23513, 20 - 23£200.00£300.00£2,400.00£2,900.00
Invoice Payment Details
Cell Formulas
RangeFormula
B3:B5B3=SUMPRODUCT(--(TEXT(Invoices!F$11:F$299,"mmmm yyyy")=Formula!C2))
C3:C5C3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2))
D3:D5D3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)
E3:E5E3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!L$11:L$299)
F3:F5F3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!M$11:M$299)
R3:R5,G3:G5G3=SUM(D3:F3)


2021 Yearly Club Accounts.xlsm
BCDEFGHIJKLMN
9Invoice Number Column BReceipt/ Statement No Column CInvoice Amount Column DDate Invoiced Column EDue Date Column FDate Paid Column GNameIncome SourceInvoice Amout OutstandingCash Column KCheque Column LBACS Column MPosted Line Total
10£8,240.00£3,100.00£2,225.00£425.00£2,490.00£0.00
110001123£125.0004/01/2118/01/2120/01/21Company A£0.00£125.00£0.00
1200020£125.0004/01/2118/01/2124/01/21Company B£0.00£125.00£0.00
1300030£1,500.0004/01/2131/01/2131/03/21Company C (QTR 1)£0.00£1,500.00£0.00
1400040£1,500.0004/01/2128/02/2128/02/21Company C (QTR 2)£0.00£1,500.00£0.00
1500050£1,500.0004/01/2131/03/2101/04/21Company C (QTR 3)£1,500.00£0.00
1600060£1,500.0004/01/2130/04/2100/01/00Company C (QTR 4)£1,500.00£0.00
1700070£90.0010/02/2112/02/2100/01/00Company D£0.00£90.00£0.00
1800080£400.0010/02/2115/02/2118/02/21Company E£0.00£400.00£0.00
1900090£100.0020/02/2125/02/2125/02/21Company F£100.00£0.00
2000100£200.0020/02/2120/05/2101/03/21Company G£0.00£200.00£0.00
2100110£300.0020/02/2103/03/2103/03/21Company H£0.00£300.00£0.00
2200120£400.0001/03/2103/03/2103/03/21Company I£0.00£100.00£300.00£0.00
2300130£500.0004/03/2110/03/2110/03/21Company J£0.00£200.00£300.00£0.00
2400140£0.0010/03/2100/01/0000/01/00Company K£0.00£0.00
2500150£0.0005/04/2100/01/0000/01/00Company L£0.00£0.00
2600160£0.0005/04/2100/01/0000/01/00Company M£0.00£0.00
2700170£0.0005/04/2100/01/0000/01/00Company N£0.00£0.00
2800180£0.0005/04/2100/01/0000/01/00Company O£0.00£0.00
2900190£0.0028/04/2105/05/2100/01/00Company P£0.00£0.00
3000200£0.0000/01/0000/01/0000/01/000£0.00£0.00
Invoices
Cell Formulas
RangeFormula
D10D10=SUMIF(D11:D40,"<>#N/A")
K10:N10K10=SUM(K11:K299)
C11:C30C11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,10,FALSE)
D11:D30D11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,8,FALSE)
E11:E30E11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,5,FALSE)
F11:F30F11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,6,FALSE)
G11:G30G11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,7,FALSE)
H11:H30H11=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,3,FALSE)
J10J10=SUMIF(J11:J299,"<>#N/A")
J11:J30J11=D11-K11-L11-M11
N11:N30N11=SUM(O11:AN11)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J22Cell Value>0textNO
J24Cell Value>0textNO
J11:J21,J23,J25:J33,J35:J40Cell Value>0textNO
N11:N40Cell Value<>$D11textNO
Cells with Data Validation
CellAllowCriteria
C11:C30Custom=YEAR(B4)=YEAR(TODAY())
I11:I30List=Rng_Income


2021 Yearly Club Accounts.xlsm
ABC
12021MonthMonth & Year
2JanuaryJanuary 2021
3FebruaryFebruary 2021
4MarchMarch 2021
5AprilApril 2021
6MayMay 2021
7JuneJune 2021
8JulyJuly 2021
9AugustAugust 2021
10SeptemberSeptember 2021
11OctoberOctober 2021
12NovemberNovember 2021
13DecemberDecember 2021
Formula
Cell Formulas
RangeFormula
C2:C13C2=CONCATENATE(B2," ",$A$1)


Invoices Issued.xlsx
ABCDEFGHIJ
1Inv NoYearWhomUsageDate InvoicedDue DateDate PaidIncome AmountExpenses AmountReceipt/ Statement Number
20001234567£8,240.00£0.0010
300010Company ACoporate04/01/2118/01/2120/01/21£125.00123
400020Company BCoporate04/01/2118/01/2124/01/21£125.00
500030Company C (QTR 1)Car Park04/01/2131/01/2131/03/21£1,500.00
600040Company C (QTR 2)Car Park04/01/2128/02/2128/02/21£1,500.00
700050Company C (QTR 3)Car Park04/01/2131/03/2101/04/21£1,500.00
800060Company C (QTR 4)Car Park04/01/2130/04/21£1,500.00
900070Company DGround Hire10/02/2112/02/21£90.00
1000080Company ENets10/02/2115/02/2118/02/21£400.00
1100090Company FNets20/02/2125/02/2125/02/21£100.00
1200100Company GNets20/02/2120/05/2101/03/21£200.00
1300110Company HNets20/02/2103/03/2103/03/21£300.00
1400120Company INets01/03/2103/03/2103/03/21£400.00
1500130Company JNets04/03/2110/03/2110/03/21£500.00
1600140Company KNets10/03/21
1700150Company LNets05/04/21
1800160Company MNets05/04/21
1900170Company NNets05/04/21
2000180Company ONets05/04/21
2100190Company PGround Hire28/04/2105/05/21
2021
Cell Formulas
RangeFormula
H2:I2H2=SUM(H3:H102)
B3:B21B3=R$1


I hope that the above will help.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,840
Office Version
  1. 365
Platform
  1. Windows
Your formula seems to work when I try it. My answers below in column B are not going to match as you have a VLOOKUP in column F of Invoices which came over as #N/A when I copied that sheet. I did type in some dates and it worked, so I then setup a VLOOKUP to see if that caused an issue, but it still worked.

I would suggest you breakdown the SUMPRODUCT(--(TEXT(Invoices!F$11:F$229,"mmmm yyyy")=Formula!C2)) formula to see if it is returning what you expect.
Maybe just look at a small sample.
TEXT(Invoices!F$11:F$22,"mmmm yyyy") and see what it returns. You can highlight the formula and press F9 key to see the array it returns. Noticed I just made the range F11:F22
Then see what
Formula!C2 returns.
If this returns what you expect then look at your VLOOKUP formula.

Book1
ABCD
1Monthly Invoice payments for 2021
2MonthInvoices Due for payment in the monthInvoices paid in the monthCash
3January3#N/A#REF!
4February1#REF!#REF!
5March10#REF!#REF!
Invoice Payment Details
Cell Formulas
RangeFormula
B3:B5B3=SUMPRODUCT(--(TEXT(Invoices!F$11:F$229,"mmmm yyyy")=Formula!C2))
C3:C5C3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2))
D3:D5D3=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)


Book1
ABCDEFGHIJKLMNOPQ
9Invoice Number Column BReceipt/ Statement No Column CInvoice Amount Column DDate Invoiced Column EDue Date Column FDate Paid Column GNameIncome SourceInvoice Amout OutstandingCash Column KCheque Column LBACS Column MPosted Line Total
10020030090000
111#N/A#N/A#N/A#N/A1/12/2021#N/A#N/A1250LookupTable
122#N/A#N/A#N/A#N/A1/14/2021#N/A#N/A125011/12/2021
133#N/A#N/A#N/A#N/A1/31/2021#N/A#N/A1500021/14/2021
144#N/A#N/A#N/A#N/A2/8/2021#N/A#N/A1500031/31/2021
155#N/A#N/A#N/A#N/A3/31/2021#N/A#N/A042/8/2021
166#N/A#N/A#N/A#N/A3/1/2021#N/A#N/A0
177#N/A#N/A#N/A#N/A3/4/2021#N/A#N/A900
Invoices
Cell Formulas
RangeFormula
C10C10=SUMIF(D19:D48,"<>#N/A")
J10:M10J10=SUM(K19:K307)
B11:B17B11=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,10,FALSE)
C11:C17C11=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,8,FALSE)
D11:D17D11=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,5,FALSE)
E11:E17E11=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,6,FALSE)
F11:F14F11=VLOOKUP(A11,$P$12:$Q$15,2,0)
G11:G17G11=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,3,FALSE)
I10I10=SUMIF(J19:J307,"<>#N/A")
I11:I17I11=D19-K19-L19-M19
M11:M17M11=SUM(O19:AN19)
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
159
Office Version
  1. 2013
Platform
  1. Windows
AhoyNC,

I am away from my PC until the morning, I will try out your recommendations tomorrow and report back.

Once again, thank you for your time and recommendations.
 

Forum statistics

Threads
1,141,606
Messages
5,707,360
Members
421,503
Latest member
Rickys03

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
Top