count and sum up

[maniknandi]

I have a database like this:

City Project Status Amount
A Project A Open 3419
A Project B Closed 5407
B Project C Progress 2351
A Project D Closed 2185
B Project E Open 5501

my requirement is like this:
In a data validation under City 3 option A, B and "All".
If I select Äll, it will count the total no of projects Under Open, Closed & Progress
If I select A, it will count the total no of projects under Open, Closed & Progress (A only)
and similarly sum up the project cost

Thanks is advance.

 

[bosco_yip]

Try,

1] Count H4, copied across

=COUNTIFS($A:$A,IF($F$3="All","<>",$F$3),$B:$B,"<>",$C:$C,H$3)

2] Sum H5, copied across

=SUMIFS($D:$D,$A:$A,IF($F$3="All","<>",$F$3),$B:$B,"<>",$C:$C,H$3)

Regards
Bosco