Conditional 'counta' argument (dependent on year)

L

Lau99

New Member
Joined
Nov 12, 2014
Messages
3
Hi all,

I'm having trouble with the argument below....

[TABLE="width: 420"]
<tbody>[TR]
[TD]2005[/TD]
[TD]2005[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2008[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Jan[/TD]
[TD]Jun[/TD]
[TD]Jan[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


I want to find out how may years hold positive counts (the count itself, nor the number of months with counts, matter). The following code works if I select the range for a single year......

=IF(COUNTA(A1:C1)>0,1,0)

.... but I have multiple spreadsheet, and there is no standard number of results per year. Ideally, I'd like to find one line of code that will do the above (give a '1' if there has been a positive result within the selected range of cells), but make it dependent on the year so that I don't have to manually select the range for each year. So far I can only think how to do this with one formula per year, so still several formulas but no need to select the range for that year (does that make sense??)

What I want it to say is: 'If row one = 2005, return 1 if cells in row 2 >1, else return 0'

I feel like this should work but it doesnt.....

=IF(1:1=2005,(COUNTA(2:2)>0,1),0)

In addition, if anyone can see how to do this in one simple formula to incorporate all years, that would be amazing!!!!

Sorry for the long winded explanation,

Thanks,
Laura
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Why are you checking row 2, that just contains month names, are you just trying to check for any value greater than 0 in any given year?
 
Upvote 0
Argh sorry, formulas amended below:

I need to know how many years have a value great than zero (in any month) - so the answer would be 4 for the first three rows, and 3 for the last row......the way I'm trying to do it at the minute is to have a separate cell for each year, then add them up at the end.

Lau99 said:
Hi all,

I'm having trouble with the argument below....

[TABLE="width: 420"]
<TBODY>[TR]
[TD]2005
[/TD]
[TD]2005
[/TD]
[TD]2005
[/TD]
[TD]2006
[/TD]
[TD]2006
[/TD]
[TD]2007
[/TD]
[TD]2008
[/TD]
[TD]2008
[/TD]
[TD]2008
[/TD]
[TD]2009
[/TD]
[/TR]
[TR]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Jan
[/TD]
[TD]Jun
[/TD]
[TD]Jan
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Feb
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]0[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1
[/TD]
[/TR]
</TBODY>[/TABLE]


I want to find out how may years hold positive counts (the count itself, nor the number of months with counts, matter). The following code works if I select the range for a single year......

=IF(COUNTA(A3:C3)>0,1,0)

.... but I have multiple spreadsheet, and there is no standard number of results per year. Ideally, I'd like to find one line of code that will do the above (give a '1' if there has been a positive result within the selected range of cells), but make it dependent on the year so that I don't have to manually select the range for each year. So far I can only think how to do this with one formula per year, so still several formulas but no need to select the range for that year (does that make sense??)

What I want it to say is: 'If row one = 2005, return 1 if cells in row 2 >1, else return 0'

I feel like this should work but it doesnt.....

=IF(1:1=2005,(COUNTA(3:3)>0,1),0)

In addition, if anyone can see how to do this in one simple formula to incorporate all years, that would be amazing!!!!

Sorry for the long winded explanation,

Thanks,
Laura
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
IF AND dates calculation
Replies
4
Views
97
yorkam
Y
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
206
JV0710
J
P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
345
DanteAmor
DanteAmor
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
228
Thlama
T
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
322
Trixterz
T

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top