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
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
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
Posted by IML on June 21, 2001 7:50 AM
Oops
I think I misunderstood. Do you have a little sample data you could provide?
Posted by Denis on June 21, 2001 7:52 AM
Re: Oops
Yes, how do i send it through?
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.
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
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
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.
