MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count like contents???


Posted by Mark on January 08, 2002 6:19 PM

Should be easy, but I'm no where near being proficient with writing macros.

In column A, sheet#1 that has several different numbers in it, with most of the same numbers being listed more than once.

I need to, first, enter the number I need a count of in cell D13 of sheet#2 (lets say the number is 123), get a count of how many "123's" are listed in column A, sheet#1, the enter the sum in cell D14 of sheet#2.

Anyone have any ideas?? Thanks


Posted by paul-johnson on January 08, 2002 6:31 PM

You could do it with a counitif and an array formula and avoid the macro. There is a great write-up in tips that explains array formulas. Mr Excel calls it "CSE formulas"

Posted by Tom Urtis on January 08, 2002 9:21 PM

As Paul noted, a macro isn't necessary. Try entering the following formula in cell D14 of Sheet2, which will count the number of times the value in cell D13 of Sheet2 shows up in column A of Sheet1.

=COUNTIF(Sheet1!A:A,"="&D13)

If I misunderstood your question, please repost.

Tom Urtis