Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

A similar Column totalling question

Posted by Denis on June 21, 2001 7:45 AM

Thanks for your help with my last problem. Now I need to total the number of cells with a certain text value if a corersponding cell contains a figure.
Thanks again.
Denis

Check out our Excel Resources

Re: A similar Column totalling question

Posted by IML on June 21, 2001 7:47 AM

I predict mark will beat me this time. Use

=COUNTIF(A1:A3,"bird")

to count the number of birds in A1 through A3

Re: A similar Column totalling question

Posted by Denis on June 21, 2001 7:49 AM

Yes, but i only want to count the number "birds" if there is a figure in a cell next to it.
Denis

Oops

Posted by IML on June 21, 2001 7:50 AM

I think I misunderstood. Do you have a little sample data you could provide?

Re: Oops

Posted by Denis on June 21, 2001 7:52 AM

Yes, how do i send it through?

Re: A similar Column totalling question

Posted by IML on June 21, 2001 7:53 AM

Use the formula
=SUM((A1:A3="bird")*(ISNUMBER(B1:B3)))
As this is an array formula, hit control shift and enter at the same time. You know you've done it correctly if brackets are placed around the formula by excel.

Good luck.

Re: A similar Column totalling question

Posted by Denis on June 21, 2001 7:58 AM

I can't get it to work, I'm not really sure what you mean about control, shift and enter at the same time.
D

Re: A similar Column totalling question

Posted by Aladin Akyurek on June 21, 2001 7:59 AM

Trying to beat Ian on this one...

Array-enter

=SUM((A1:A22="bird")*(ISNUMBER(B1:B22)))

In order to arrayenter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

I interpreted "a figure" as any number. I interested in a particular number, change

ISNUMBER(B1:B22) to B1:B22=45 where 45 is an example.

Aladin

Re: A similar Column totalling question

Posted by IML on June 21, 2001 8:02 AM

If you are typing it in, don't just hit enter. Hit enter while control and shift are depressed.
If you are copying and pasting it, hit F2, then enter whil control and shift are depressed.
If you are getting a #value! error, you are very close.

Hope that helps.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.