I am having a problem where when I enter in the below formula I get an error message saying I`ve entered too many arguments for this function.
=COUNTA('TAB2'!H3:L3,'TAB2'!P3:U3,'TAB2'!Y3:AC3,'TAB2'!AG3:AI3,'TAB2'!AM3:AQ3)/COLUMNS('TAB2'!H3:L3,'TAB2'!P3:U3,'TAB2'!Y3:AC3,'TAB2'!AG3:AI3,'TAB2'!AM3:AQ3)
Can anyone help me?
Thank you
What is this? COLUMNS
What are you trying to do?
HTH
Regards, Jeff
need to know exactly what you are trying to accomplish here...looks like you are trying to count non blank cells in rows, from several worksheets, then divide that by some value calculated from several several rows/worksheets?
so i`m trying to work out the % of cells completed in a separate tab...
Well, would need a sample of what you have, with what the problem is and what you expect to see...what you put in original post is pretty vague and without some supporting info, I can't figure out what you are looking for..sorry
sorry, here is an example. I need to calculate the % of cells completed in the second tab but i only want to take into account the columns that are not pink....does that make sense? and when i mean complete i mean not blank!!!!
The error was caused due to the multiple ranges in single COLUMNS function.Originally Posted by ea223
Formula:
COLUMNS('TAB2'!H3:L3,'TAB2'!P3:U3,'TAB2'!Y3:AC3,'TAB2'!AG3:AI3,'TAB2'!AM3:AQ3)
The above formula should be written as
Formula:=(COLUMNS('tab2'!H3:L3)+COLUMNS('tab2'!P3:U3)+COLUMNS('tab2'!Y3:AC3)+COLUMNS('tab2'!AG3:AI3)+COLUMNS('tab2'!AM3:AQ3))
Hope this helps!
Last edited by :) Sixthsense :); 10-28-2012 at 11:47 PM. Reason: Formula Tag Applied
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
okay, I loaded your example, have got some drop down lists for each column, but no data or formulas to SEE what you are trying to accomplish, maybe i'm missing something, but i think you'll need to talk to someone else, sorry if you feel I am wasting your time, but i have no idea what i'm dealing with here..
it would have been better if you had actually populated you're sample with some data.
this isnt pretty, but give it a shot...
=SUM('tab2'!B2:G2,'tab2'!I2:M2,'tab2'!O2:S2,'tab2'!U2:Y2,'tab2'!AA2:AE2,'tab2'!AG2:AK2,'tab2'!AM2:AQ2,'tab2'!AS2:AV2)=COUNTA('tab2'!B2:G2,'tab2'!I2:M2,'tab2'!O2:S2,'tab2'!U2:Y2,'tab2'!AA2:AE2,'tab2'!AG2:AK2,'tab2'!AM2:AQ2,'tab2'!AS2:AV2)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Copy and paste the below formula in A2 cell of Tab1
Formula:=COUNTA('tab2'!B2:G2,'tab2'!I2:M2,'tab2'!O2:S2,'tab2'!U2:Y2,'tab2'!AA2:AE2,'tab2'!AG2:AK2,'tab2'!AM2:AQ2,'tab2'!AS2:AV2)/(COLUMNS('tab2'!B2:G2)+COLUMNS('tab2'!I2:M2)+COLUMNS('tab2'!O2:S2)+COLUMNS('tab2'!U2:Y2)+COLUMNS('tab2'!AA2:AE2)+COLUMNS('tab2'!AG2:AK2)+COLUMNS('tab2'!AM2:AQ2)+COLUMNS('tab2'!AS2:AV2))
Drag the A2 cell formula down.
Format the Column-A as Percentage.
Hope this helps!
Last edited by :) Sixthsense :); 10-29-2012 at 12:26 AM. Reason: Attachment Included
perfect thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks