Boolean True/False values not recognised in Countifs formula

[Higenbo]

Hi,

I am running an SQL statement query via Azure directly into Excel.

The data is from Salesforce and some of the columns contain Boolean values shown as true or false (left aligned all lower case).

An example of the data set is below:

Salesman Converted Date
Fred true 02/01/2018
Jim false 05/01/2018
Billy true 06/01/2018


I want to count the number of instances of true for the month of January i.e. 2 conversions. When I compile a Countifs formula it doesn't recognise 'True' in my range.

If I click in the cells and hit Enter the true becomes TRUE and centre aligned. Then the formula works.

Any help converting the cells out of Boolean within a Countif formula would be really helpful.

Thanks

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(--$B$2:$B$4,--($C$2:$C$4-DAY($C$2:$C$4)+1=DATE(2018,1,1)))

 

[Higenbo]

Hi,

Sorry should have mentioned.

I have date drivers so users can select a from and to date and then the countifs bring back the values from the data set for that period.

So my countifs currently looks like:

=Countifs(Date Column,">="&To Date Criteria Cell, Date Column,"<="&From Date Criteria Cell,Converted,"TRUE")

Can I use Sumproduct with changing criteria?

Thanks

 

[Aladin Akyurek]

Hi,

Sorry should have mentioned.

I have date drivers so users can select a from and to date and then the countifs bring back the values from the data set for that period.

So my countifs currently looks like:

=Countifs(Date Column,">="&To Date Criteria Cell, Date Column,"<="&From Date Criteria Cell,Converted,"TRUE")

Can I use Sumproduct with changing criteria?

Thanks

=SUMPRODUCT(--$B$2:$B$4,--($C$2:$C$4-DAY($C$2:$C$4)+1=J2))

where J2 houses a first day date like 1/1/2018 indicative of a month of interest.