Too Few Arguments Error

[mcgonma]

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","-")

 

[Scott T]

What exactly are you trying to do?

 

[mcgonma]

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 .

 

[Scott T]

the syntax of COUNTIFS is

=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

[=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]

 

[mcgonma]

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.

 

[Scott T]

Match is a lookup function and will return the row or column number of the match.

The syntax of match is

[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
	A	B	C	D	E	F
1	Elephant	Lion	Tiger	Dog	Crab	gorilla
2
3		range has dog?
4		yes
Sheet5

Cell Formulas
Range	Formula
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.

 

[mcgonma]

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.

 

[Rick Rothstein]

While the ranges are different, this appears to be the same question that this OP asked here...

https://www.mrexcel.com/forum/excel...thin-large-range-post5062995.html#post5062995

 

[Scott T]

Match has the syntax

[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.

=IF(ISNUMBER(MATCH(FALSE,BA4:DC4,0)),"YES","-")

 

[mcgonma]

They are similar and pertaining to the same intended accomplishment. My apologies for the duplicate entries.