COUNTIF not working

Question

I have a worksheet with risks on there for my project. Each risk has a workstream associated with it which is in one column and then another column with a formula in it to show whether it is open and overdue (either TRUE or FALSE) so it looks something like the below, but with many more columns and rows:

WORKING SHEET
A	B	C
Workstream	Description	Open and Overdue?
Business Process	xyxyxyx	TRUE
Change	vbvbvbvbv	FALSE

I then have have a summary sheet where I am trying to count by workstream how many risks each workstream has that are open and overdue in the raw data sheet, i have written the following formula but i just get VALUE error back

=COUNTIFS('working sheet'!A4:A100,A2,'working sheet'!C4:C100,"TRUE)

The A2 above refers to the name of the workstream in my summary sheet (e.g. Business Process)

When i seperate the two formulas - i.e. =COUNTIF for each criteria the formula works but obviously i need to combine them as I dont just want to know how many business process risks there are but how many are open and overdue.

Answer 1

You have a typo in your formula.   The formula will display  either

Text

Boolean

Unknown

here is the formula again: 

=IF(ISTEXT(A1),"Text",IF(ISLOGICAL(A1),"Boolean","unkown"))

but based on what you show, it is probably boolean since it did not prove to be text.

then your countifs formula should be:

=COUNTIFS('working sheet'!A4:A100,A2,'working sheet'!C4:C100,TRUE)

--

Regards,

Tom Ogilvy

Answer 2

Thanks Tom

I applied this formula to test my third column and i got a centered Boolean"unknown in each cell so not sure what to do next?

Answer 3

You can use the ISTEXT and ISLOGICAL functions

so to test a1 you could do

=IF(ISTEXT(A1),"Text",IF(ISLOGICAL(A1),"Boolean","unkown"))

if the cell holds text it is usually left aligned while a logical value would be centered in the cell.   This can of course be changed by changing the formatting options, but may be a quick way to tell.

--

regards,

tom Ogilvy

Answer 4

Thanks Tom but how do i tell the difference between boolean or string?

Answer 5

There is the Boolean value TRUE and the string "True" or "TRUE".   Countif(s) knows the difference.  Make sure your third column has either the Boolean TRUE throughout or the string "TRUE" throughout and then use the same argument to countifs  

--

Regards,

Tom Ogilvy