SUM figure in one range if another cell opposite contains text

[JC83UK]

Hey Everyone,

I am trying to put together a Voucher system which will help us to track how much money we have taken in voucher sales and how much has been redeemed.

I need the Total Vouchers Redeemed to scan the range J2:K51 for any text (this will be dates) and if found to return the figure from Column F on the same Row.

Spa BOGOF 2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Paid Voucher		Free Voucher		Total Voucher	Total Paid	Bought By	Sold By		Redeemed A	Redemed B
2	SPA050	/A	SPA050	/B	£ 50.00	£ 25.00	Guest A	JC		01/01/2022	05/01/2022		Total Vouchers Sold	£ 350.00
3	SPA051	/A	SPA051	/B	£ 200.00	£ 100.00
4	SPA052	/A	SPA052	/B	£ 100.00	£ 50.00							Total Vouchers Redeemed
5	SPA053	/A	SPA053	/B		£ -
6	SPA054	/A	SPA054	/B		£ -
7	SPA055	/A	SPA055	/B		£ -
8	SPA056	/A	SPA056	/B		£ -
9	SPA057	/A	SPA057	/B		£ -
10	SPA058	/A	SPA058	/B		£ -
11	SPA059	/A	SPA059	/B		£ -
12	SPA060	/A	SPA060	/B		£ -
13	SPA061	/A	SPA061	/B		£ -
14	SPA062	/A	SPA062	/B		£ -
15	SPA063	/A	SPA063	/B		£ -
16	SPA064	/A	SPA064	/B		£ -
17	SPA065	/A	SPA065	/B		£ -
18	SPA066	/A	SPA066	/B		£ -
19	SPA067	/A	SPA067	/B		£ -
20	SPA068	/A	SPA068	/B		£ -
21	SPA069	/A	SPA069	/B		£ -
22	SPA070	/A	SPA070	/B		£ -
23	SPA071	/A	SPA071	/B		£ -
24	SPA072	/A	SPA072	/B		£ -
25	SPA073	/A	SPA073	/B		£ -
26	SPA074	/A	SPA074	/B		£ -
27	SPA075	/A	SPA075	/B		£ -
28	SPA076	/A	SPA076	/B		£ -
29	SPA077	/A	SPA077	/B		£ -
30	SPA078	/A	SPA078	/B		£ -
31	SPA079	/A	SPA079	/B		£ -
32	SPA080	/A	SPA080	/B		£ -
33	SPA081	/A	SPA081	/B		£ -
34	SPA082	/A	SPA082	/B		£ -
35	SPA083	/A	SPA083	/B		£ -
36	SPA084	/A	SPA084	/B		£ -
37	SPA085	/A	SPA085	/B		£ -
38	SPA086	/A	SPA086	/B		£ -
39	SPA087	/A	SPA087	/B		£ -
40	SPA088	/A	SPA088	/B		£ -
41	SPA089	/A	SPA089	/B		£ -
42	SPA090	/A	SPA090	/B		£ -
43	SPA091	/A	SPA091	/B		£ -
44	SPA092	/A	SPA092	/B		£ -
45	SPA093	/A	SPA093	/B		£ -
46	SPA094	/A	SPA094	/B		£ -
47	SPA095	/A	SPA095	/B		£ -
48	SPA096	/A	SPA096	/B		£ -
49	SPA097	/A	SPA097	/B		£ -
50	SPA098	/A	SPA098	/B		£ -
51	SPA099	/A	SPA099	/B		£ -
52					TOTAL	£ 175.00
SPA100 - SPA149

Cell Formulas
Range	Formula
N2	N2	=SUM(E2:E51)
F2:F51	F2	=SUM(E2/2)
F52	F52	=SUM(F2:F51)

 

[Eric W]

Try this in N4:

=SUMPRODUCT(F2:F51*SIGN((J2:J51<>"")+(K2:K51<>"")))

 

[JC83UK]

Absolutely Amazing! That solved it. Than you very much for the help on this one.