Set up macro (or formula)
 

Hello,

Ive 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

Set up macro (or formula)
 

With data in Sheet1

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

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

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Moin" wrote:

Hello,

Ive 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

Set up macro (or formula)
 

Hi,


What you seem to have asked for is this

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

But do you mean this

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

Mike

"Moin" wrote:

Hello,

Ive 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