Too Few Arguments Error

M

mcgonma

Board Regular
Joined
Nov 2, 2011
Messages
162
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I have the following formula that, when I add one more cell to it, I get an error message that "You've added too few arguments for this function". But as soon as I delete "CR3," after "CM3" the formula works.

=IF(COUNTIFS(AM3,AO3,AT3,AU3,AY3,BG3,BK3,BO3,BP3,BQ3,BR3,BT3,BU3,BW3,BY3,CC3,CG3,CH3,CM3,FALSE),"YES","-")
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Well, let me give you some background.

I'm working with a file that has all 38,000 employees of a company in it to identify who is impacted by some implementation projects.

Columns AK:CT each represent a specific type of criteria to identify if the employee in that row might be impacted. The formula =ISERROR(MATCH("*Acquisition*",$B3:$AC3,0)) is in each column of AK:CT (and down through the 38,000 rows of employees) looks back through the respective row to tell me if that employee meets the criteria of that specific column and should be considered as an impacted employee. For example, in the formula i just provided, if the employee has the word "acquisition" anywhere in the information about them in B:AC (e.g., position title, parent organization unit, sub org unit, manager, manager's manager, etc. etc.), it returns a "FALSE" value and that employee is consider impacted by the project.

Then, I'm trying to build an entire other set of columns to drill down further into those people who have a "FALSE" value for some of the specific criteria. So, I was using the formula =IF(COUNTIFS(AM3,AO3,AT3,AU3,AY3,BG3,BK3,BO3,BP3,BQ3,BR3,BT3,BU3,BW3,BY3,CC3,CG3,CH3,CM3,FALSE),"YES","-") to tell me if the employee has a "FALSE" value in any of the specific columns.

Honestly, creating what I am doing in excel is starting to become very problematic because I have many many more criteria to add and many many more slices and dices of the data that I will need to do. I keep hearing that I really should move this all to database. The only problem is that I don't know how to format the data to get it into a database, and don't know how to build the queries in the database after that. And, don't really know where to look for support on accomplishing that either :-D.
 
Upvote 0
the syntax of COUNTIFS is
Code: 
=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

Where range is where to look and criteria is the criteria for that range. You would specify the range and criteria in pairs. For example this would test if AM3, AO3, AT3 all = false. If they are all false then it returns 1 if one or more of them is not false then it returns 0, countifs operates on and logic, everything must be true to be counted

Code: 
[=COUNTIFS(AM3,FALSE,AO3,FALSE,AT3,FALSE)/CODE]


Instead of countifs you may want to use isnumber and match. If false is in the range then it will return a number and isnumber will be true. If false in not in the range is number will be false.


[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel 2010[/XH][/XR][XR][XH][/XH][XH]AM[/XH][XH]AN[/XH][XH]AO[/XH][XH]AP[/XH][XH]AQ[/XH][XH]AR[/XH][XH]AS[/XH][XH]AT[/XH][/XR][XR][XH]3[/XH][XD=h:l]k[/XD][XD=h:r][/XD][XD=h:l]l[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r]FALSE[/XD][/XR][XR][XH]4[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]5[/XH][XD=h:r|cls:fx][FORMULA==COUNTIFS(AM3,FALSE,AO3,FALSE,AT3,FALSE)]0[/FORMULA][/XD][XD=h:l]=COUNTIFS(AM3,FALSE,AO3,FALSE,AT3,FALSE)[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]6[/XH][XD=h:r|cls:fx][FORMULA==ISNUMBER(MATCH(FALSE,AM3:AT3,0))]TRUE[/FORMULA][/XD][XD=h:l]=ISNUMBER(MATCH(FALSE,AM3:AT3,0))[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]AM5[/XD][XD]=COUNTIFS(AM3,FALSE,AO3,FALSE,AT3,FALSE)[/XD][/XR][XR][XD]AM6[/XD][XD]=ISNUMBER(MATCH(FALSE,AM3:AT3,0))[/XD][/XR][/RANGE]
 
Upvote 0
Hi Scott...thank you very much for this solution. It seems like a very elegant approach. My challenge is that I don't fully understand how to use it.
 
Upvote 0
Match is a lookup function and will return the row or column number of the match.

The syntax of match is
Code: 
[COLOR=#2F2F2F][FONT="Segoe UI"]MATCH(lookup_value, lookup_array, [match_type])[/FONT][/COLOR]
Lookup_value: what you want to lookup
Lookup_array: where you want to look for it
Match_type: optional default is approximate match, same as vlookup. you can also specify exact match or approximate match greater than.

for more information on match see
https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

Say you wanted to know if dog was in the range A1:F1

Excel 2010
ABCDEF
1ElephantLionTigerDogCrabgorilla
2
3range has dog?
4yes
Sheet5
Cell Formulas
RangeFormula
B4=IF(ISNUMBER(MATCH("dog",A1:F1,0)),"yes","-")


Since dog is in the 4th column match returns 4 and since 4 is a number is number isnumber returns true. This cause IF to return the true argument. If dog was not in the range then is number would be false and IF would return the false argument.
 
Upvote 0
I don't seem to be able to get that to work when I'm looking to see if certain specific columns within the larger range contain the "FALSE" value.

This is what I have in the cell for AF
=iF(ISNUMBER(MATCH(FALSE,BA4,BC4,BH4,BI4,BL4,BU4,CC4,CD4,CF4,CG4,CJ4,CL4,CN4,CR4,CV4,CW4,DC4,0)),"YES","-")

I get an error message that I have entered too many arguments for this function, and it highlights "BH4" in the formula bar.
 
Upvote 0
Match has the syntax
Code: 
[COLOR=#2F2F2F][FONT="Segoe UI"]MATCH(lookup_value, lookup_array, [match_type])[/FONT][/COLOR]

The lookup_array should be a single range.

depending on what is in the other columns this may work.

Code: 
=IF(ISNUMBER(MATCH(FALSE,BA4:DC4,0)),"YES","-")
 
Upvote 0
They are similar and pertaining to the same intended accomplishment. My apologies for the duplicate entries.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Error message when copying and renaming a worksheet
2
Replies
13
Views
297
smozgur
smozgur
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
314
bdt
B
S
  • Solved
Avoid each third cell while copying or pasting a range.
2
Replies
11
Views
730
Peter_SSs
Peter_SSs
D
  • Question Question
Too few arguments entered for function
Replies
1
Views
1K
Joe4
Joe4
D
  • Solved
concatenate numbers, replacing blanks with comma across entire rows
2
Replies
13
Views
2K
Dave01
D

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

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