Excel formula question

[Bizkit87]

Hi

I want to use COUNTIFS(RANGE, CRITERIA), but i'm getting errors:

Here is what i want to do:

There are +- 200 columns with number in [the specific numbers for this formula is in column B,E,H,K etc. So every 3rd column)

On a different sheet, i only want to use those number in every 3rd Row, and if they are between 50 and 100, they should be counted, otherwise not.

I haven't done formulas in a while, so help would be greatly appreciated.

 

[Jladan]

Have u tried naming your range correctly, it should only refer to the columns you want to add, try the R1C1 formatting style as it make repetative formulas like this easy.

The other way to do it is to rebuild the range (columns) on a second sheet with just the numbers from the columns you want and running the formula on that. Its then easy to return the result to whereever u would like

You could also so this easily via VBA but I have no idea how comfortable you would be with that...

 

[Jladan]

Something else jsut occured to me, when naming the criteria don't foget your quotes. ie >50 should be ">=50" and the second criteria should be "<=100". You will have to name the range twice, once for each criteria

 

[Bengalaas]

=SUMPRODUCT((MOD(COLUMN(A1:J1),3)=0)*(A1:J1))

You can change where it starts adding by adjusting the "=0" to either "=1" or "=2" (using the MOD() function)

http://www.excelforum.com/excel-worksheet-functions/620111-count-every-other-cell.html

 

[Bizkit87]

Something else jsut occured to me, when naming the criteria don't foget your quotes. ie >50 should be ">=50" and the second criteria should be "<=100". You will have to name the range twice, once for each criteria

thanx for the feedback

ok what exactly do you mean define twice

if i say

Countifs(sheet2!B5,">=50",Sheet2!B5,"<100")

won't this count the number that are bigger or equal to 50 (so that will then be n +1), but also will then count 20, as the second rule says <100 ?

 

[Jladan]

no, it will return the count of only those that satisfy both rules. Its kinda like an AND rule.

Count all in the range that are over or equal 50. now of those count all of those under or equal to 100. this is now your result.

However in excel just check one or two rows to be sure

 

[Bizkit87]

no, it will return the count of only those that satisfy both rules. Its kinda like an AND rule.

Count all in the range that are over or equal 50. now of those count all of those under or equal to 100. this is now your result.

However in excel just check one or two rows to be sure

thanks, ok i got it to work without compiling new list [so i still just count every 3rd row),and it seems to work:

=COUNTIFS(Sheet2!B5:Sheet2!E5:Sheet2!H5:Sheet2!K5:Sheet2!N5:Sheet2!Q5:Sheet2!T5:Sheet2!W5:Sheet2!Z5:Sheet2!AC5:Sheet2!AF5:Sheet2!AI5:Sheet2!AL5:Sheet2!AO5:Sheet2!AR5:Sheet2!AU5:Sheet2!AX5:Sheet2!BA5:Sheet2!BD5:Sheet2!BG5,">=50",Sheet2!B5:Sheet2!E5:Sheet2!H5:Sheet2!K5:Sheet2!N5:Sheet2!Q5:Sheet2!T5:Sheet2!W5:Sheet2!Z5:Sheet2!AC5:Sheet2!AF5:Sheet2!AI5:Sheet2!AL5:Sheet2!AO5:Sheet2!AR5:Sheet2!AU5:Sheet2!AX5:Sheet2!BA5:Sheet2!BD5:Sheet2!BG5,"<100")

Thanks for the help guys, much appreciated.