"You've entered too many arguments for this function" Error message

[Pookiemeister]

Since our I.T. announced that macros can no longer be used, I have to tried to write this code in the formula line using nested "if" statements but I get the error message stated in the Subject line. Here is what I have written.

=IF(Q13=1,COUNTIF('WO Week 1'!B:B,"Support Order (ZM08)"),Q13=2,COUNTIF('WO Week 2'!B:B,"Support Order (ZM08)",Q13=3,COUNTIF('WO Week 3'!B:B,"Support Order (ZM08)",Q13=4,COUNTIF('WO Week 4'!B:B,"Support Order (ZM08)",Q13=5,COUNTIF('WO Week 5'!B:B,"Support Order (ZM08)")))

Q13 contains this code

=ROUNDUP(DAY(NOW()) / 7, 0)

Thank you

 

[RoryA]

You seem to have confused the syntax of IFS with the syntax of IF. IF only accepts 3 arguments. You could simply replace IF with IFS in your formula but you should be aware that IFS will evaluate every single argument passed to it whether it needs to or not, so a nested IF or CHOOSE would be more efficient.

 

[KOKOSEK]

you do not close brackets after couple of COUNTIFs and You should use IFS not IF.
Try:

=IFS(Q13=1,COUNTIF('WO Week 1'!B:B,"Support Order (ZM08)"),Q13=2,COUNTIF('WO Week 2'!B:B,"Support Order (ZM08)"),Q13=3,COUNTIF('WO Week 3'!B:B,"Support Order (ZM08)"),Q13=4,COUNTIF('WO Week 4'!B:B,"Support Order (ZM08)"),Q13=5,COUNTIF('WO Week 5'!B:B,"Support Order (ZM08)"))

 

[RoryA]

One option:

=IF(AND(Q13>=1,Q13<=5),COUNTIF(CHOOSE(Q13,'WO Week 1'!B:B,'WO Week 2'!B:B,'WO Week 3'!B:B,'WO Week 4'!B:B,'WO Week 5'!B:B),"Support Order (ZM08)"),"")

 

[Pookiemeister]

By doing what you suggested worked. Thank you.

 

[Pookiemeister]

I ran into a problem I thought this might be issue, but I wasn't sure, so I didn't mention it here and the logic part of my brain is overheated trying to figure this issue out. The issue would occur at the beginning of every month. Based on the formula below, and since most months have approximately 5 weeks in them, if a person were to enter a value for week 5 in the new month, the formula would show 1 instead of 5. This is where I am stuck, any ideas? Thank you.

=ROUNDUP(DAY(NOW()) / 7, 0)