sumif

[Gusher]

Bank Name	GBP equivalent
Bank A	36,551
Bank A	646,489
Bank A	2,200,000
Bank A	2,088
Bank A	45,092
Bank B	41,644
Bank B	313,002
Bank B	234
Bank B	40,361
Bank C	11,400
Bank C	5,678
Bank C	789

1. I need a formula that for a specified bank name will return the number of accounts wihtin a specified account size. For example for bank B count number of accounts greater than 2000 GBP and less than 4000 GBP.

2. Then a formula that sums the value all those accounts in the size 2000 GBP - 4000 GBP.

Many Thanks

Mark

 

[Kaper]

=SUMIFS(B:B,A:A,"Bank B",B:B,">=2000",B:B,"<=4000")

Note that in the sample it will be 0. The formula sums also threshold values (2000 and 4000) if you want to exclude them, change >= and <= to > and <

 

[Joe4]

None of the record in your example meet all three criteria:
- column A is "Bank B"
- column B is between 2000 and 4000

So the total would be 0.

I created an example below that has 2 record meeting the criteria. You would use a SUMIFS function, which allows multiple conditions:

Formula in cell E3:

=SUMIFS(B3:B14,A3:A14,D3,B3:B14,">=" &D4,B3:B14,"<="&D5)

 

[Kaper]

PS. As you use Microsoft 365 you may easily prepare single formula to calculate whole dynamic list for all banks. But for sample data this list will include only one bank

So test it with changed data as shown above. You may also include references to cells containing limits as shown by @Joe4 Notice that instead of ">=2000" a bit more advanced notation is used. Let's assume minimum value is in C1. You can't write just ">=C1" but have to concatenate texts >= with content of C1 cell. So use ">=" &C1

(Joe4 used D4 but in example below it could easily cause spill error).

=GROUPBY(A1:A14,B1:B14,SUM,3,0,,(B1:B14>=2000)*(B1:B14<=4000))

Zeszyt1
	A	B	C	D	E
1	Bank Name	GBP equivalent		Bank Name	GBP equivalent
2				Bank A	2088
3	Bank A	36551
4	Bank A	646489
5	Bank A	2200000
6	Bank A	2088
7	Bank A	45092
8	Bank B	41644
9	Bank B	313002
10	Bank B	234
11	Bank B	40361
12	Bank C	114
13	Bank C	5678
14	Bank C	789
Arkusz1

Cell Formulas
Range	Formula
D1:E2	D1	=GROUPBY(A1:A14,B1:B14,SUM,3,0,,(B1:B14>=2000)*(B1:B14<=4000))
Dynamic array formulas.

 

[igold]

I think you also wanted a count of the Banks that fit your criteria. How about this:

INCOME.xlsm
	AH	AI	AJ	AK
1	Bank Name	GBP equivalent	Count	Sum
2
3	Bank A	36,551	0	0
4	Bank A	646,489
5	Bank A	2,200,000
6	Bank A	2,088
7	Bank A	45,092
8	Bank B	41,644
9	Bank B	313,002
10	Bank B	234
11	Bank B	40,361
12	Bank C	11,400
13	Bank C	5,678
14	Bank C	789
PruCalc

Cell Formulas
Range	Formula
AJ3	AJ3	=COUNTIFS(AH3:AH14,"Bank B",AI3:AI14,">2000",AI3:AI14,"<4000")
AK3	AK3	=SUMIFS(AI3:AI14,AH3:AH14,"Bank B",AI3:AI14,">2000",AI3:AI14,"<4000")

 

[Kaper]

I think that formula in AJ3 is not per se for banks counting. It will return the number of accounts for given Bank which fulfill that criteria (0, as is seen in output in case when Bank B has no such accounts).

 

[igold]

For example for bank B count number of accounts greater than 2000 GBP and less than 4000 GBP.

 

[Kaper]

Indeed, I focused only on second part. But too have full list with accounts count (per bank) GroupBy would be also fine.

=GROUPBY(A1:A14,B1:B14,COUNTA,3,0,,(B1:B14>=2000)*(B1:B14<=4000))

I'm not sure how to force Groupby to use two functions at once, so my (far-from-perfect-but-working ) all in one formula would be:

=HSTACK(GROUPBY(A1:A14,B1:B14,COUNTA,3,0,,(B1:B14>=2000)*(B1:B14<=4000)),CHOOSECOLS(GROUPBY(A1:A14,B1:B14,SUM,3,0,,(B1:B14>=2000)*(B1:B14<=4000)),2))

 

[Fluff]

You can get the count & sum like

Fluff.xlsm
	A	B	C	D	E	F
1	Bank Name	GBP equivalent			SUM	COUNT
2				Bank A	2088	1
3	Bank A	36,551		Bank B	6240	2
4	Bank A	646,489
5	Bank A	2,200,000
6	Bank A	2,088
7	Bank A	45,092
8	Bank B	3,900
9	Bank B	313,002
10	Bank B	2340
11	Bank B	40,361
12	Bank C	11,400
13	Bank C	5,678
14	Bank C	789
Data

Cell Formulas
Range	Formula
D1:F3	D1	=GROUPBY(A1:A100,B1:B100,HSTACK(SUM,COUNT),0,0,,(B1:B100>=2000)*(B1:B100<=4000))
Dynamic array formulas.