Counting both negative and postive numbers in 1 formula

[Neavalmi]

Hi Excel Experts,

I have two columns, A contains Planned LOC Years (2019,2020 and 2021, no plans ,etc), B savings in dollars sometime neg (with savings) and sometimes postive (no savings) and blanks or sometime also zero.

I want to count in the summary sheet all line with savings both negative and postive per YEAR

Column A B
2019 2,000
2019 -100
2019 3,000
2020 -500
2021 0
No Plans
Dropped

Summary
Planned LOC Year Parts with Savings
2019 3
2020 1
2021 0
No Plans 0
Dropped 0

Hope my explanation is clear.

Thanks excel experts

 

[Michael M]

Try

=SUMPRODUCT((A1:A5=2019)*(B1:B5<>0))

change the year to suit

 

[ravisingh]

You could also use a pivot table with 'Count' option for the data area - this would make it easier to refresh the summary - you wont need to add a formula every time a new year/category is added to Col A.

 

[Aladin Akyurek]

Try this which should be a tad faster:

=SUM(COUNTIFS(A:A,2019,B:B,{">0","<0"}))