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

[Kayslover]

Hi All,

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

=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.

 

[AhoyNC]

For E2 try:

Book2
	A			D	E	F	G				K
1
2	January 2021				8
3
10
11							1/2/2021				1
12							1/8/2021				2
13							2/6/2021				3
14							3/4/2021				4
15							1/21/2021				5
16							2/10/2021				6
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(TEXT($G$11:G40,"mmmm yyyy")=A2),K11:K40)

 

[Kayslover]

AhoyNC,

Firstly, Thank you for your time and effort.

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

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Kayslover]

AhoyNC,

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

I initially had the formula

=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

=SUMPRODUCT(--(TEXT(Invoices!F$11:F$299,"mmmm yyyy")=Formula!C2))

Where Formula!C2 has January 2021 and is made up using

=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

=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

=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]

Your formula works for me.

Formula Sheet

Book2
	A	B	C
1	2021
2		January	January 2021
3			8
Formula

Cell Formulas
Range	Formula
C2	C2	=CONCATENATE(B2," ",$A$1)
C3	C3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)

Invoices Sheet

Book2
	G	H	I	J	K
10	Date				Amount
11	1/2/2021				1
12	1/8/2021				2
13	2/6/2021				3
14	3/4/2021				4
15	1/21/2021				5
16	2/10/2021				6
Invoices

 

[Kayslover]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Monthly Invoice payments for 2021
2	Month	Invoices Due for payment in the month	Invoices paid in the month	Cash	Cheques	BACS	Totals				Invoices Due for payment in the month	Invoices Due for payment in the month Row Numbers	Invoices paid in the month	Invoices paid in the month Row Numbers	Cash	Cheques	BACS	Totals
3	January	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A				3	11 -13	2	11 - 12	£125.00	£125.00	£0.00	£250.00
4	February	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A				4	14, 17 - 19	3	14, 18 - 19	£1,900.00	£0.00	£90.00	£1,990.00
5	March	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A				4	15, 21 - 23	5	13, 20 - 23	£200.00	£300.00	£2,400.00	£2,900.00
Invoice Payment Details

Cell Formulas
Range	Formula
B3:B5	B3	=SUMPRODUCT(--(TEXT(Invoices!F$11:F$299,"mmmm yyyy")=Formula!C2))
C3:C5	C3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2))
D3:D5	D3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)
E3:E5	E3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!L$11:L$299)
F3:F5	F3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!M$11:M$299)
R3:R5,G3:G5	G3	=SUM(D3:F3)

2021 Yearly Club Accounts.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
9	Invoice Number Column B	Receipt/ Statement No Column C	Invoice Amount Column D	Date Invoiced Column E	Due Date Column F	Date Paid Column G	Name	Income Source	Invoice Amout Outstanding	Cash Column K	Cheque Column L	BACS Column M	Posted Line Total
10			£8,240.00						£3,100.00	£2,225.00	£425.00	£2,490.00	£0.00
11	0001	123	£125.00	04/01/21	18/01/21	20/01/21	Company A		£0.00	£125.00			£0.00
12	0002	0	£125.00	04/01/21	18/01/21	24/01/21	Company B		£0.00		£125.00		£0.00
13	0003	0	£1,500.00	04/01/21	31/01/21	31/03/21	Company C (QTR 1)		£0.00			£1,500.00	£0.00
14	0004	0	£1,500.00	04/01/21	28/02/21	28/02/21	Company C (QTR 2)		£0.00	£1,500.00			£0.00
15	0005	0	£1,500.00	04/01/21	31/03/21	01/04/21	Company C (QTR 3)		£1,500.00				£0.00
16	0006	0	£1,500.00	04/01/21	30/04/21	00/01/00	Company C (QTR 4)		£1,500.00				£0.00
17	0007	0	£90.00	10/02/21	12/02/21	00/01/00	Company D		£0.00			£90.00	£0.00
18	0008	0	£400.00	10/02/21	15/02/21	18/02/21	Company E		£0.00	£400.00			£0.00
19	0009	0	£100.00	20/02/21	25/02/21	25/02/21	Company F		£100.00				£0.00
20	0010	0	£200.00	20/02/21	20/05/21	01/03/21	Company G		£0.00		£200.00		£0.00
21	0011	0	£300.00	20/02/21	03/03/21	03/03/21	Company H		£0.00			£300.00	£0.00
22	0012	0	£400.00	01/03/21	03/03/21	03/03/21	Company I		£0.00		£100.00	£300.00	£0.00
23	0013	0	£500.00	04/03/21	10/03/21	10/03/21	Company J		£0.00	£200.00		£300.00	£0.00
24	0014	0	£0.00	10/03/21	00/01/00	00/01/00	Company K		£0.00				£0.00
25	0015	0	£0.00	05/04/21	00/01/00	00/01/00	Company L		£0.00				£0.00
26	0016	0	£0.00	05/04/21	00/01/00	00/01/00	Company M		£0.00				£0.00
27	0017	0	£0.00	05/04/21	00/01/00	00/01/00	Company N		£0.00				£0.00
28	0018	0	£0.00	05/04/21	00/01/00	00/01/00	Company O		£0.00				£0.00
29	0019	0	£0.00	28/04/21	05/05/21	00/01/00	Company P		£0.00				£0.00
30	0020	0	£0.00	00/01/00	00/01/00	00/01/00	0		£0.00				£0.00
Invoices

Cell Formulas
Range	Formula
D10	D10	=SUMIF(D11:D40,"<>#N/A")
K10:N10	K10	=SUM(K11:K299)
C11:C30	C11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,10,FALSE)
D11:D30	D11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,8,FALSE)
E11:E30	E11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,5,FALSE)
F11:F30	F11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,6,FALSE)
G11:G30	G11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,7,FALSE)
H11:H30	H11	=VLOOKUP(B11,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,3,FALSE)
J10	J10	=SUMIF(J11:J299,"<>#N/A")
J11:J30	J11	=D11-K11-L11-M11
N11:N30	N11	=SUM(O11:AN11)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J22	Cell Value	>0	text	NO
J24	Cell Value	>0	text	NO
J11:J21,J23,J25:J33,J35:J40	Cell Value	>0	text	NO
N11:N40	Cell Value	<>$D11	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C11:C30	Custom	=YEAR(B4)=YEAR(TODAY())
I11:I30	List	=Rng_Income

2021 Yearly Club Accounts.xlsm
	A	B	C
1	2021	Month	Month & Year
2		January	January 2021
3		February	February 2021
4		March	March 2021
5		April	April 2021
6		May	May 2021
7		June	June 2021
8		July	July 2021
9		August	August 2021
10		September	September 2021
11		October	October 2021
12		November	November 2021
13		December	December 2021
Formula

Cell Formulas
Range	Formula
C2:C13	C2	=CONCATENATE(B2," ",$A$1)

Invoices Issued.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Inv No	Year	Whom	Usage	Date Invoiced	Due Date	Date Paid	Income Amount	Expenses Amount	Receipt/ Statement Number
2	0001	2	3	4	5	6	7	£8,240.00	£0.00	10
3	0001	0	Company A	Coporate	04/01/21	18/01/21	20/01/21	£125.00		123
4	0002	0	Company B	Coporate	04/01/21	18/01/21	24/01/21	£125.00
5	0003	0	Company C (QTR 1)	Car Park	04/01/21	31/01/21	31/03/21	£1,500.00
6	0004	0	Company C (QTR 2)	Car Park	04/01/21	28/02/21	28/02/21	£1,500.00
7	0005	0	Company C (QTR 3)	Car Park	04/01/21	31/03/21	01/04/21	£1,500.00
8	0006	0	Company C (QTR 4)	Car Park	04/01/21	30/04/21		£1,500.00
9	0007	0	Company D	Ground Hire	10/02/21	12/02/21		£90.00
10	0008	0	Company E	Nets	10/02/21	15/02/21	18/02/21	£400.00
11	0009	0	Company F	Nets	20/02/21	25/02/21	25/02/21	£100.00
12	0010	0	Company G	Nets	20/02/21	20/05/21	01/03/21	£200.00
13	0011	0	Company H	Nets	20/02/21	03/03/21	03/03/21	£300.00
14	0012	0	Company I	Nets	01/03/21	03/03/21	03/03/21	£400.00
15	0013	0	Company J	Nets	04/03/21	10/03/21	10/03/21	£500.00
16	0014	0	Company K	Nets	10/03/21
17	0015	0	Company L	Nets	05/04/21
18	0016	0	Company M	Nets	05/04/21
19	0017	0	Company N	Nets	05/04/21
20	0018	0	Company O	Nets	05/04/21
21	0019	0	Company P	Ground Hire	28/04/21	05/05/21
2021

Cell Formulas
Range	Formula
H2:I2	H2	=SUM(H3:H102)
B3:B21	B3	=R$1

I hope that the above will help.

 

[AhoyNC]

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
	A	B	C	D
1	Monthly Invoice payments for 2021
2	Month	Invoices Due for payment in the month	Invoices paid in the month	Cash
3	January	3	#N/A	#REF!
4	February	1	#REF!	#REF!
5	March	10	#REF!	#REF!
Invoice Payment Details

Cell Formulas
Range	Formula
B3:B5	B3	=SUMPRODUCT(--(TEXT(Invoices!F$11:F$229,"mmmm yyyy")=Formula!C2))
C3:C5	C3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2))
D3:D5	D3	=SUMPRODUCT(--(TEXT(Invoices!G$11:G$299,"mmmm yyyy")=Formula!C2),Invoices!K$11:K$299)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
9	Invoice Number Column B	Receipt/ Statement No Column C	Invoice Amount Column D	Date Invoiced Column E	Due Date Column F	Date Paid Column G	Name	Income Source	Invoice Amout Outstanding	Cash Column K	Cheque Column L	BACS Column M	Posted Line Total
10			0						200	300	900	0	0
11	1	#N/A	#N/A	#N/A	#N/A	1/12/2021	#N/A		#N/A	125			0			Lookup	Table
12	2	#N/A	#N/A	#N/A	#N/A	1/14/2021	#N/A		#N/A		125		0			1	1/12/2021
13	3	#N/A	#N/A	#N/A	#N/A	1/31/2021	#N/A		#N/A			1500	0			2	1/14/2021
14	4	#N/A	#N/A	#N/A	#N/A	2/8/2021	#N/A		#N/A	1500			0			3	1/31/2021
15	5	#N/A	#N/A	#N/A	#N/A	3/31/2021	#N/A		#N/A				0			4	2/8/2021
16	6	#N/A	#N/A	#N/A	#N/A	3/1/2021	#N/A		#N/A				0
17	7	#N/A	#N/A	#N/A	#N/A	3/4/2021	#N/A		#N/A			90	0
Invoices

Cell Formulas
Range	Formula
C10	C10	=SUMIF(D19:D48,"<>#N/A")
J10:M10	J10	=SUM(K19:K307)
B11:B17	B11	=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,10,FALSE)
C11:C17	C11	=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,8,FALSE)
D11:D17	D11	=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,5,FALSE)
E11:E17	E11	=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,6,FALSE)
F11:F14	F11	=VLOOKUP(A11,$P$12:$Q$15,2,0)
G11:G17	G11	=VLOOKUP(B19,'[Invoices Issued.xlsx]2021'!$A$3:$J$152,3,FALSE)
I10	I10	=SUMIF(J19:J307,"<>#N/A")
I11:I17	I11	=D19-K19-L19-M19
M11:M17	M11	=SUM(O19:AN19)

 

[Kayslover]

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.