Countifs greater or less than full column

[LouSmidge]

I need to count in a column on another tab in the full column for any values greater than 1 or less than -1. I have created an IF formula followed by Countifs for the look up detail i need to count but this formula is returning 0.

Any help greatly appreciated.

=IF(OR(COUNTIFS(Analysis!AB:AB,"<=-1"),COUNTIFS(Analysis!AB:AB,">=1")),COUNTIFS(Analysis!T:T,CFOii!B4,Analysis!AJ:AJ,CFOii!B9),0)

 

[Eric W]

Welcome to the MrExcel forum!

Your question is a little vague, but try:

=SUM(COUNTIFS(Analysis!AB:AB,{"<=-1",">=1"},Analysis!T:T,CFOii!B4,Analysis!AJ:AJ,CFOii!B9))

 

[LouSmidge]

Apologies for my vague question but that solution has now counted the items, so thanks for that.

I'm trying to count the movement of assets year on year by their delivery price. Col AnalysisAB is the year 2024 and if this has a negative delivery price balance meaning it pushed its delivery to 2025.

I now have the count from your suggestion above but how do I show that a negative balance in Col AB should return a negative sum and vice versa for a positive?

Thanks again!

 

[Eric W]

So you're saying that if there are 3 negative values, and 10 positive values, you want a result of 7? Try:

=SUM(COUNTIFS(Analysis!AB:AB,{"<=-1",">=1"},Analysis!T:T,CFOii!B4,Analysis!AJ:AJ,CFOii!B9)*{-1,1})

 

[LouSmidge]

That worked thanks so much for your help!