Need an formula to count text values

February 4, 2011 at 15:48:13
Specs: Windows 7
I am trying to create a formula to work with text entries only the way that the SUMIF formula works with numbers.

I will try my best to explain exactly what I need. Basically I need to keep a record of all the electrical cables I have installed according to their size.

I have three columns on my spreadsheet A,B and C and say 10 rows for example.

Column A contains details of each cable location
Column B contains details of each cable size, 10mm, 25mm, etc.
Column C contains the date each cable was installed

What I want to do is say count how many 10mm cables I have installed by simply counting the completed cells with dates in column C but only if the adjacent cell in column B says 10mm.

I hope this makes sense

Thanks

Marc


See More: Need an formula to count text values

Report •


#1
February 4, 2011 at 19:35:26
What version of Excel are you using?

If it's 2007 or 2010, look up the COUNTIFS formula in the Help files.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 5, 2011 at 00:42:29
I am using 2003, I have researched this a little further on the web and found the following formula which works if I specify a specific date, but i am unsure how to change the criteria in C1:C10 to just count entries in the cells as opposed to a specific date.

See below

{=SUM((C1:C10="specific date")*(B1:B10="10mm))}

will this formula work if I can change the criteria where I have typed specific date to calculate any text entries or is there a better formula I could use.

This is really frustrating and I feel I am missing something simple.

Thanks

Marc


Report •

#3
February 5, 2011 at 00:46:22
Apologies

I typed the formula slightly wrong, should read

{=SUM((C1:C10="specific date")*(B1:B10="10mm"))}

I missed the " after 10mm.

Thanks

Marc


Report •

Related Solutions

#4
February 5, 2011 at 13:28:13
What about this, array entered, of course:

{=SUM((C1:C12<>"")*(B1:B12="10mm"))}

or, better yet, this non-array version:

=SUMPRODUCT((C1:C12<>"")*(B1:B12="10mm"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 7, 2011 at 09:51:41
Yes this works fine

Thanks for all your help.

Marc

P.S. Just for future reference what do the <> and "" represent.


Report •

#6
February 7, 2011 at 12:33:33
= Equals
> Greater Than
< Less Than
<> Not Equal To

(Standard mathematical operators)

0 Zero
" " Space
"" Nothing

(Those are all very different things in Excel)

C1<>""

C1 Not Equal To Nothing

That means contains something.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question