You've Entered too many arguments

[Woofaloo]

I am trying to track a percentage over 10 items and an overall percentage of 4 sets of 10 items.

For the percentage of each section of 10 items, I use the following:
In cell O5 I entered =COUNTA(E5:N5)/COLUMNS(E5:N5)
In cell Z5 I entered =COUNTA(P5:Y5)/COLUMNS(P5:Y5)
In cell AK5 I entered =COUNTA(AA5:AJ5)/COLUMNS(AA5:AJ5)
And in cell AV5 I entered =COUNTA(AL5:AU5)/COLUMNS(AL5:AU5)
These work perfect.


Now in cell AW5 I want to see an overall percentage of the combined 4 sections of 10 items.
I used =(COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5))/(COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)) but I get "You've entered too many arguments"

Can someone please help me with this formula if it can be done?
TIA

 

[RoryA]

Welcome to the forum.

It works for me but it will of course only ever return 1 or an error.

 

[Marcelo Branco]

I think you need something like
=COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)/SUM(COLUMNS(E5:N5),COLUMNS(P5:Y5),COLUMNS(AA5:AJ5),COLUMNS(AL5:AU5))

M.

 

[Woofaloo]

I think you need something like
=COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)/SUM(COLUMNS(E5:N5),COLUMNS(P5:Y5),COLUMNS(AA5:AJ5),COLUMNS(AL5:AU5))

M.

Absolutely PERFECT!!!! I am so in LOVE with the forum!!!!

 

[Woofaloo]

Ok. Now I have a new "Too many arguments" question.
Using this formula =COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)/SUM(COLUMNS(E5:N5),COLUMNS(P5:Y5),COLUMNS(AA5:AJ5),COLUMNS(AL5:AU5)) to calculate all sections for one overall percentage is perfect.
Now in each of the 10 cells I have entered =sum('sheet2'!c5) to pull a date into one of the 10 cells on sheet 1. But the formula is now seeing each of the 10 cells as not blank and including the cells with a formula in the percentage. Is it possible to calculate a percentage in each section and for all 4 sections while ignoring blank cells even though each contain a formula that pulls from another sheet?

I have tried adding ,">""" or ,"?*"but gives me too many argument statement

 

[Jonmo1]

=COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)/SUM(COLUMNS(E5:N5),COLUMNS(P5:Y5),COLUMNS(AA5:AJ5),COLUMNS(AL5:AU5))

How about
=(COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5))/(COLUMNS(E5:AU5)-3)

 

[Jonmo1]

Ok, take the first part counta
COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)

What is in the 'Between' cells, O5 Z5 and AK5 ?

 

[Marcelo Branco]

How about
=(COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5))/(COLUMNS(E5:AU5)-3)

Yes, much simpler.
Noting that the four ranges have the same number of columns, i had thought of something as simple as
=COUNTA(E5:N5,P5:Y5,AA5:AJ5,AL5:AU5)/(4*COLUMNS(E5:N5))
but i thought it best to suggest a generic formula.

M.

 

[Woofaloo]

The cells in between calculate the percentage based on the 10 cells in each of 4 sections. However, once a formula in each of the 10 cells (pulling information from another page) the % calculation changes to over 100% even though the cells are visually blank.

 

[Jonmo1]

So if the 3 between cells are a percentage (they are decimal numbers between 0 and 1) ?
And the other cells Are Dates, then we should be able to do this

=COUNTIF(E5:AU5,">1")/(COLUMNS(E5:AU5)-3)