Set up macro (or formula)

[Moin]

Hello,

I’ve the following scenario.

I need to either set a formula or a macro to identify in the separate sheet
to find; the total count for ‘done’ and ‘in progress’ for all items A – E.

Please help me to understand. Appreciate any help,



Item Plan comp status
A 11/1/2009 done
A 11/2/2009 done
A 12/20/2009 in progress
B 1/1/1010 in progress
B 11/10/2009 done
C 11/20/2009 done
C 1/1/1010 in progress
C 1/1/1011 in progress
C 1/1/1012 in progress
D 12/1/2009 done
D 12/2/2009 done
E 12/3/2009 done
E 12/4/2009 done
E 12/4/2009 in progress

 

[Jacob Skaria]

With data in Sheet1

'Count of A with status Done
=SUMPRODUCT((Sheet1!A1:A20="A")*(Sheet1!C1:C20="Done"))

'with criteria in cell A1 of sheet2
=SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet1!C1:C20="Done"))

Count of A with status Done and In Progress
=SUMPRODUCT((Sheet1!A1:A20="A")*(Sheet1!C1:C20={"Done","In Progress"}))

or
=SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet1!C1:C20={"Done","In Progress"}))

If this post helps click Yes

 

[Mike H]

Hi,


What you seem to have asked for is this

=COUNTIF(Sheet1!C2:C15,"Done")+COUNTIF(Sheet1!C2:C15,"In Progress")

But do you mean this

=SUMPRODUCT((Sheet1!A2:A15="A")*(Sheet1!C2:C15="Done"))

Mike