Efficient Formula for Summing Values Based on Multiple Conditions

[irfman]

Hi everyone,

I have a large dataset stored in an Excel table, which includes many columns, but for my current query, I am focusing on the following sample structure:

Name	Type	FB	Amount
Sam	A	Y	100
Sam	A	N	50
Sam	B	Y	200
Sam	B	Y	10
Tom	A	N	30
Tom	A	N	50
Tom	B	Y	75
Tom	B	N	50

I have extracted unique names in Column F using the following formula:

=UNIQUE(db[Name])

Now, I need to retrieve the corresponding sum of Amount for each Type (A & B) where FB is "Y" (Yes), structured as follows:

Name	A	B
Sam	100	210
Tom	0	75

I have tried using SUMIFS, and while it works, my dataset is quite large, making the worksheet painfully slow.

I would also like to know if using a helper column could help reduce computations and improve performance.

Can someone suggest an efficient Excel formula to achieve this without slowing down performance?

I’d really appreciate your support and suggestions. Thanks in advance!

 

[kvsrinivasamurthy]

Timesheet.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Name	Type	FB	Amount		Name	A	B
3		Sam	A	Y	100		Sam	100	210
4		Sam	A	N	50		Tom	0	75
5		Sam	B	Y	200
6		Sam	B	Y	10
7		Tom	A	N	30
8		Tom	A	N	50
9		Tom	B	Y	75
10		Tom	B	N	50
11
Sheet2

In G3

=LET(a,UNIQUE(B3:B10),b,SUMIFS(E3:E10,C3:C10,H2:I2,D3:D10,"Y",B3:B10,a),c,SUMIFS(E3:E10,C3:C10,I2,D3:D10,"Y",B3:B10,a),HSTACK(a,b,c))

 

[kvsrinivasamurthy]

Since 2021 version is not having Hstack, Use these functions. Data range is in Post#2.
In G2

=UNIQUE(B3:B10)

In H2

=LET(a,UNIQUE(B3:B10),SUMIFS(E3:E10,C3:C10,H2:I2,D3:D10,"Y",B3:B10,a))

 

[kvsrinivasamurthy]

Formula in Post#2 should be read as below
In G2

=LET(a,UNIQUE(B3:B10),b,SUMIFS(E3:E10,C3:C10,H2:I2,D3:D10,"Y",B3:B10,a),HSTACK(a,b))

 

[Georgiboy]

If you are looking for speed, have you tried a Pivot Table?

This situation is perfect for a PT:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Name	Type	FB	Amount			FB	Y
2	Sam	A	Y	100
3	Sam	A	N	50			Sum of Amount	Column Labels
4	Sam	B	Y	200			Row Labels	A	B	Grand Total
5	Sam	B	Y	10			Sam	100	210	310
6	Tom	A	N	30			Tom		75	75
7	Tom	A	N	50			Grand Total	100	285	385
8	Tom	B	Y	75
9	Tom	B	N	50
Sheet1

 

[Peter_SSs]

I have tried using SUMIFS, and while it works, my dataset is quite large, making the worksheet painfully slow.

I have not tested with large data but wondering if, given that you have already tried SUMIFS and that was slow (& same function suggested by @kvsrinivasamurthy ), that using Excel's built-in Pivot Table feature, with a few tweaks, might be worth trying?

irfman_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Type	FB	Amount			Sum of Amount		Type
2	Sam	A	Y	100			Name	FB	A	B
3	Sam	A	N	50			Sam	Y	100	210
4	Sam	B	Y	200			Tom	Y	0	75
5	Sam	B	Y	10
6	Tom	A	N	30
7	Tom	A	N	50
8	Tom	B	Y	75
9	Tom	B	N	50
10
Sheet1