Counting datas in one range based on datas in another range

[Irene]

A B C D E F
1 T B 0.00
2 H 400.00
3 C V 1,000.00
4 C B 1,000.00
5 C B 1,000.00
6 C E 1,000.00
7 E 300.00
8 T H V 50.00
9 C H 1,000.00
10 C E 1,000.00
11 T V 20.00
12 C B 1,000.00
13 C B 1,000.00
14 E 500.00
5 3 4 3


Totals C T Deposits
Business 5
Home 3
Education 4
Vehicle 3

column A has the status of C for Completed or T for Terminated. Column B to
E are the initials for Business, Home, Education and Vehicle, and the last
column is the deposit amount for each. I would like to know how many in my
column B for business that completed and terminated, and also the total of
deposits made for business. The same with the Home, Education and Vehicles.

Any help, please.

 

[ShaneDevenshire]

Hi,

Assuming your data setup. And assuming the totals area starts in A16 with
Business in A17 and Totals in B16.

The formula for Totals, B17 is
=COUNTIF(B$1:E$14,LEFT(A17))
This can be copied down

The formula for C, C17 is
=SUMPRODUCT(--($A$1:$A$14=C$16),--(INDEX($B$1:$E$14,,ROW(A1))=LEFT($A17)))
This can be copied down and over to column D

The formula for Deposits, E17 is
=SUMPRODUCT(--(INDEX($B$1:$E$14,,ROW(B1))=LEFT($A17)),$F$1:$F$14)
This can be copied down.

If these help, please click the Yes button.