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 installedWhat 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

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.

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

Apologies I typed the formula slightly wrong, should read

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

I missed the " after 10mm.

Thanks

Marc

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.

Yes this works fine Thanks for all your help.

Marc

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

= 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.

Ask Your Question

Weekly Poll

Do you think the EU antitrust ruling against Google was fair?

Discuss in The Lounge

Poll History