Count Formula 1

[robcarr]

Dear All,

I am trying to do a formula to count the contents of column c, if column A criteria is is met and then if column b criteria is met, I am using this formula for a 2 way count , it counts the number of instances of cell B5 if call C4 is met.
=SUM(('sheet1'!a$1:a$281=$C$4)*('sheet1'!b$1:b$281=B5)), is it possible to do the same but to check 2 columns and if they both are met count the number of instancesi in column c. Also I need to do a count on all items that have a specific word, but can contain other words in the cell, for example, I need to count the no. of times changed appears in column c.
the cell can contain changed by, to be change, etc, The Changed word can be at the start of the cell data, or middle or end.

Can anyone help as this is really frustraing me.

Thanks

 

[DaleWatson123321]

Rob,

I've completed a portion of your task, using the DCOUNT function. However, I'm a little puzzled by your description, so was wondering if you could email me that section of your file.

I'll "finish it off" and email it back.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

In answer to your "Change" question, this should do:
=COUNTIF(c1:c281,"*Change*&quot

For the other query, you can easily do a count using multiple criteria, but I'm not quite sure I understand what you want. Please reply if the following assumption is correct.
Sum the contents of column C where colA=something AND colB=somethingelse

or

do you want to count the instances of a certain value in C where colA = something and colB=something else ??

Rgds
Geoff

 

[robcarr]

XLBO,
thanks for helping, I need to count the number of instances in cloumn c, second part of your question.

 

[robcarr]

XLbo,

The *change*, will be the value in column c, when the other 2 columns are met. Sorry about the confusion, I do tend to waffle a bit.

Thanks for all the help.

 

[xlbo]

This formula needs to be array entered - ie use ctrl + shift + enter instead of "return"
=SUM((A1:A281=1)*(B1:B281=1)*(IF(ISERROR(FIND("change",C1:C281)),0,1)))

----all one line

Obviously, substitute your own criteria. This one above will count where A1:A281=1 AND B1:B281=1 AND C1:C281 has the word "change" somewhere in it

HTH
Geoff

 

[robcarr]

Geoff,

Thanks for that you have saved me a lot of time.
Regards