Excel Counting Command

April 2, 2009 at 05:04:03
Specs: Windows XP, 512MB
Hiya this is my first post regarding counting, i want to count the number of old codes sent and recieved (as well as for the other codes) how can this be done in excel see below for values. the problem is the blanks.

Code Sent Received
Old Numeric Numeric
Old Done Done
Old 16_02_2009
Old Done Done
Q1 Done Done
Q1 16_02_2009
Q1 N/A N/A
Q1 16_01_2009 16_01_2009
Q1 Numeric Numeric
Q1
Old 19_02_2009
Old 27_02_2009
Old
Old 10_03_2009 10_03_2009
Old Done Done
New 12_03_2009 12_03_2009
New
New 12_03_2009
New 02_03_2009 12_03_2009
New
New 17_03_2009 19_03_2009
New N/A N/A
Q1 Done Done
Q1 Done Done
Q1 N/A N/A
Q1 17_03_2009
Q1 Done Done
Q1 17_03_2009 17_03_2009
Q1
Q1
Q1 N/A N/A


See More: Excel Counting Command

Report •


#1
April 2, 2009 at 05:19:35
Unless you want to do this with VB/VBA, you're probably better off posting this in the office forum. However:

If I understand correctly, you're trying to get a count of how many rows have "Old" in column A, and do NOT have a blank in either column B or column C.

If this is right, try:

=SUMPRODUCT(--($A$1:$A$1000="Old"),--($B$1:$B$1000<>""),--($C$1:$C$1000<>""))

Hope this helps


Report •

#2
April 2, 2009 at 05:40:02
Yeh it works thanks

Report •

#3
April 2, 2009 at 06:55:22
Jus another question i want know if the codes have been completed i have used a COUNTA formula so it gives me a number at the end (4 being completed). but i want to know how many completed in each codes?

see below


Code Created by Sent Received
Old PU Numeric Numeric 4
Old AB Done Done 4
Old PU 16_02_2009 3
Old AB Done Done 4
Q1 PU Done Done 4
Q1 AB 16_02_2009 3
Q1 PU N/A N/A 4
Q1 AB 16_01_2009 16_01_2009 4
Old PU 19_02_2009 3
Old AB 27_02_2009 3
Old PU 2


Report •

Related Solutions


Ask Question