How to use the countif() function to count items in a table

[Wellie]

I have a table contains multple columns of info as shown
in the below
AcctID ProjectName PMName Phase Status
EIG Project 1 Wellie Initiation New
MLI Project 2 John Develop Active
EIG Project 11 Wellie Qualify Active
EIG Project UUU John Design InActive
MLI Project XX Carol Deployment Completed
MLI Project YYY Carol Design Active

I'd like to use the countif() function to complete the
following metric such that I know how many projects for
each status for each AcctID.
EIG MLI
New 1 0
Active 1 2
InActive 1 0
OnHold 0 0
Completed 0 1

Thanks in advance for any assistance.

 

[JulieD]

Hi Wellie

assuming your data as displayed below is in range A1:E7
and the result range is A9:C14

the formula you need in B10 is
=SUMPRODUCT(--($A$2:$A$7=B$9),--($E$2:$E$7=$A10))
this can then be filled down

the formula you need in C10 is
=SUMPRODUCT(--($A$2:$A$7=C$9),--($E$2:$E$7=$A10))
this can then be filled down

Hope this helps
Cheers
JulieD

 

[JulieD]

i'm glad

thanks for the feedback

It works.

Have a great day.