Conditional 'counta' argument (dependent on year)

[Lau99]

Hi all,

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

2005	2005	2005	2006	2006	2007	2008	2008	2008	2009
Jan	Feb	Mar	Jan	Jun	Jan	Jan	Feb	Mar	Feb
1	2	0	0	0	3	1	4	4	2
2	3	5	5	0	9	9	0	0	0
1	2	3	0	0	3	1	4	4	2
2	3	0	0	0	4	0	0	0	1

<tbody>
</tbody>

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

 

[Scott Huish]

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?

 

[Lau99]

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.

Hi all,

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

2005	2005	2005	2006	2006	2007	2008	2008	2008	2009
Jan	Feb	Mar	Jan	Jun	Jan	Jan	Feb	Mar	Feb
1	2	0	0	0	3	1	4	4	2
2	3	5	5	0	9	9	0	0	0
1	2	3	0	0	3	1	4	4	2
2	3	0	0	0	4	0	0	0	1

<TBODY>
</TBODY>

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

 

[Scott Huish]

=(countifs(1:1,2005,3:3,">0")>0)+0