Using COUNTIF when the cells contain a formula that returns the value

[soph12081]

Hello

I have a spreadsheet that i'm using at the minute and it has been working perfect. It now needs amending, and as part of this we are pulling data out of sharepoint into the excel spreadsheet, the data is then referenced in another tab. The values are then used to calculate various measures. The formula that I have at the minute is

=SUMPRODUCT(COUNTIFS('Downtime Log'!$I$5:$I$4605,$AY$4,'Downtime Log'!$A$5:$A$4605,A27,'Downtime Log'!$J$5:$J$4605,CHOOSE({1,2},$W$2,$AY$2)))

All of the cells referenced above are cells that contain formulas that return values, and it's these values that I need the formula to use to do the COUNTIF. When I manually input the data it calculates correctly, so i think it's something to do with having formulas in there? I have tried formatting etc. but nothing seems to be working. A5:A4605 is a date column if that makes any difference.

Please help, I've been trying to figure this out for two hours!

Thank you for your help.

 

[XOR LX]

Hi,

In what format are the dates after being pulled into Excel from Sharepoint?

Regards

 

[soph12081]

Hello

it's in the *26/04/2017 format.

Thanks

 

[XOR LX]

What does the asterisk there mean?

Are there are any additional spaces within those strings?

When you test one of the cells containing these imported dates with the function ISNUMBER, what is the result? For example, what is the result of:

=ISNUMBER(Downtime Log'!$A$5)

?

Regards

 

[soph12081]

Hello

the result is True.

I'm unsure why the asterick is there - its the first date format available.

Thanks

 

[XOR LX]

And I take it that the equivalent formula for the date in A27 also returns TRUE?

And you believe that the issue lies with the date column, and not the others (I and J)?

Perhaps worth checking the results of each of the three individual formulas:

=COUNTIFS('Downtime Log'!$I$5:$I$4605,$AY$4)

=COUNTIFS('Downtime Log'!$A$5:$A$4605,A27)

=SUMPRODUCT(COUNTIFS('Downtime Log'!$J$5:$J$4605,CHOOSE({1,2},$W$2,$AY$2)))

and seeing which one does not agree with your expected return for that column (as can easily be obtained via manual filtering, for example).

Regards

 

[soph12081]

Hello

Sorry for taking a few days to reply. I have done as above and the one that doesn't agree is =COUNTIFS('Downtime Log'!$A$5:$A$4605,A27) which is the date column.

When I open the formula builder, it shows the dates as a number, i think the problem lies within there but i don't know how to fix it?

I think it's because it's pulling the time through as well as the date so when it converts it to a number to be read, it doesn't match the whole number of the date. I'll see if this is the problem.

Thank you

 

[XOR LX]

Ah. Are you saying that the dates in this column contain time portions also?

For example, if you reformat them (Format Cells/Number) using a Custom number format of:

dd/mm/yyyy hh:mm:ss

do they all display with 00:00:00 for the time portion, e.g.:

28/04/2017 00:00:00

or do you have one or more which display as e.g.:

28/04/2017 01:24:42

etc.?

If so, then COUNTIFS will not consider the two equal.

I would suggest that you remove the time (i.e. decimal) portions from the imported dates, which can be done in many ways in Excel, including by using the INT function.

If you are unable or unwilling to do that, you could use:

=COUNTIFS('Downtime Log'!$A$5:$A$4605,">="&A27,'Downtime Log'!$A$5:$A$4605,"<"&A27+1)

instead, which will get round this issue.

Regards

 

[soph12081]

Thank you very much for your help!!

 

[XOR LX]

You're welcome!