Sumproduct #Value! Error due to Text

[abusuzuki]

Hello, I have this table as shown in the XL2BB, where I need to calculate the total amount of 2 columns : (C:D) based on date : column (B) , And I need to get the sum only ONCE at the last instance, I used the following sumif formula for that :
=IF(B2<>B3,SUM((D:E)*(--(B:B=B2))),"")
But the problem with this formula is that it displays Error value in place of the sum because there is text content in the field of sum, Is there any formula that has the same function as the above, but can sum the values with text in one of the sum range? Of course, I need the sum to show only once in the last instance and without repetition.

Book4.xlsx
	F
3
Sheet1

Cell Formulas
Range	Formula
F3	F3	=IF(B3<>B4,SUM((D:E)*(--(B:B=B3))),"")

 

[abusuzuki]

Book4.xlsx
	A	B	C	D	E	F
1	No.	date	invoice number	cash	check	total amount
2	1	29/12/22	93151	10
3	2	29/12/22	93152	check	1000
4	3	29/12/22	93153	10
5	4	29/12/22	93154	10		#VALUE!
6	1	30/12/22	93155	20
7	2	30/12/22	93156	20
8	3	30/12/22	93157	20
9	4	30/12/22	93158	20
10	5	30/12/22	93159	check	2000
11	6	30/12/22	93160	20		#VALUE!
12	1	31/12/22	93161
13	2	31/12/22	93162
14	3	31/12/22	93163
15	4	31/12/22	93164			#VALUE!
Sheet1

Cell Formulas
Range	Formula
F2:F15	F2	=IF(B2<>B3,SUM((D:E)*(--(B:B=B2))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E15,A1:B1,D1:F1,A16:B1048576,D16:F1048576,G:XFD,C:C	Expression	=$A1=1	text	NO

 

[Dave Patton]

The message stated SumProduct so I used Sumproduct formula .You did not show expected results so I showed the sum of the two columns.

SumProduct2022.xlsm
	A	B	C	D	E	F
1	No.	date	invoice number	cash	check	total amount
2	1	29-Dec-22	93151	10
3	2	29-Dec-22	93152	check	1000
4	3	29-Dec-22	93153	10
5	4	29-Dec-22	93154	10		1030
6	1	30-Dec-22	93155	20
7	2	30-Dec-22	93156	20
8	3	30-Dec-22	93157	20
9	4	30-Dec-22	93158	20
10	5	30-Dec-22	93159	check	2000
11	6	30-Dec-22	93160	20		2100
12	1	31-Dec-22	93161
13	2	31-Dec-22	93162
14	3	31-Dec-22	93163
15	4	31-Dec-22	93164			0
6d

Cell Formulas
Range	Formula
F2:F4	F2	=IF(B2<>B3,SUM((D:E)*(--(B:B=B2))),"")
F5:F15	F5	=IF(B5<>B6,SUMPRODUCT(--($B$2:B5=B5),$E$2:E5)+SUMPRODUCT(--($B$2:B5=B5),$D$2:D5),"")

 

[Dave Patton]

With 365, try =IF(B5<>B6,SUM(IF($B$2:B5=B5,$D$2:E5)),"")

or with most versions of Excel and without entering formulas using Data Subtotals feature

It gives option for Grand Total, total by Day, and Totals for Cash and Cheques, and full details
I show with 2 Daily totals

SumProduct2022a.xlsm
	A	B	C	D	E	F
1	No.	date	invoice number	cash	check	total amount
6		29-Dec-22 Total		30	1000	1030
13		30-Dec-22 Total		100	2000	2100
18		31-Dec-22 Total		0	0	0
19		Grand Total		130	3000	3130
20
6e

Cell Formulas
Range	Formula
D18:F18,D6:F6	D6	=SUBTOTAL(9,D2:D5)
D13:F13	D13	=SUBTOTAL(9,D7:D12)
D19:F19	D19	=SUBTOTAL(9,D2:D17)

 

[abusuzuki]

I tested both functions and they both work as they should, and even the second formula works in Excel online. I think I will use the second formula because it is short, but I will keep both of them. Thank you very much Dave Patton. Now I will start my job in the year 2023 in a distinctive way.

Book4.xlsx
	A	B	C	D	E	F
1	No.	date	invoice number	cash	check	total amount
2	1	29/12/22	93151	10
3	2	29/12/22	93152	check	1000
4	3	29/12/22	93153	10
5	4	29/12/22	93154	10		1030
6	1	30/12/22	93155	20
7	2	30/12/22	93156	20
8	3	30/12/22	93157	20
9	4	30/12/22	93158	20
10	5	30/12/22	93159	check	2000
11	6	30/12/22	93160	20		2100
12	1	31/12/22	93161
13	2	31/12/22	93162
14	3	31/12/22	93163
15	4	31/12/22	93164			0
Sheet1

Cell Formulas
Range	Formula
F2:F15	F2	=IF(B2<>B3,SUMPRODUCT(--($B$2:B2=B2),$E$2:E2)+SUMPRODUCT(--($B$2:B2=B2),$D$2:D2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E15,A1:B1,A16:B1048576,D16:F1048576,D1:XFD1,C:C,G12:XFD1048576,G2:G11,I2:XFD11	Expression	=$A1=1	text	NO