Search code examples
excelexcel-formulaexcel-2013

Applying a TRUNC() function to a range in COUNTIFS(). Or using SUMPRODUCT() instead?

I have a column (Col C) of decimal numbers. I'm trying to count how many of the entries in Col C are equal to 13 if truncated while Col B = "USA". Is it possible to apply a function to a range like this?

I've seen this question asked in different contexts and the suggestions have been to use SUMPRODUCT() somehow instead.

Here are some examples of what I've been trying:

=COUNTIFS(TRUNC(Data!C:C,0),13,Data!B:B,"USA")
=COUNTIFS(Data!C:C,TRUNC(Data!C:C,0)=13,Data!B:B,"USA")
=SUMPRODUCT(TRUNC(Data!C:C,0)=13, Data!B:B="USA")

The SUMPRODUCT() just returns #VALUE!

Solution

  • Why not just expand the COUNTIFS?

    =COUNTIFS(Data!C:C,">=13",Data!C:C,"<14",Data!B:B,"USA")