Difficult Array Formula

[GERMAN GONZALEZ]

Can anyone help me sort out a problem with an array formula? I get the following pop-up error message when I hit the Ctrl-Shift-Enter keys:

"The formula is missing an opening or closing parenthesis."

The array equation is as follows:

=IFERROR(INDEX($C$3:$C$200, MATCH(0,IF(ISBLANK($C$3:$C$200),1),COUNTIFS($C$3:$C$200,C3,$F$3:$F$200,"Yes")))

Thanks very much in advance for any help received!

 

[AhoyNC]

Try:
=IFERROR(INDEX($C$3:$C$200, MATCH(0,IF(ISBLANK($C$3:$C$200),1),COUNTIFS($C$3:$C$200,C3,$F$3:$F$200,"Yes"))),"")

 

[GERMAN GONZALEZ]

AhoyNC,

Thanks very much. I no longer get that pop-up error message. Unfortunately, the formula didn't generate the result I hoped for. It generates an empty cell. In any event, thanks again for your help. I really appreciate it!

 

[jtakw]

Can anyone help me sort out a problem with an array formula? I get the following pop-up error message when I hit the Ctrl-Shift-Enter keys:

"The formula is missing an opening or closing parenthesis."

The array equation is as follows:

=IFERROR(INDEX($C$3:$C$200, MATCH(0,IF(ISBLANK($C$3:$C$200),1),COUNTIFS($C$3:$C$200,C3,$F$3:$F$200,"Yes")))

Thanks very much in advance for any help received!

Your formula syntax is incorrect, review your formula after MATCH.

 

[Rick Rothstein]

AhoyNC,

Thanks very much. I no longer get that pop-up error message. Unfortunately, the formula didn't generate the result I hoped for. It generates an empty cell. In any event, thanks again for your help. I really appreciate it!

Not sure what additional help you are looking for as you did not tell us what the data this formula is trying to work on nor what you actually wanted for results from that data. All you said was that you formula produced a missing parenthesis error and AhoyNC told you how to make that error go away. Beyond that, he (we) have no idea what your data looks like nor what you need from it.

 

[GERMAN GONZALEZ]

Rick,
My apologies. I thought I had the array finally figured out and was just stumped by the missing parenthesis. Here's my situation: Column C contains Supplier names. Column F contains either a Yes or No entry for each Supplier name. Column F is actually OSHA Recordable Injuries charged to each Supplier via a Yes entry. I have a formula that works great at counting the number of Yes entries for each Supplier. It's =COUNTIFS($C$3:$C$200,C4,$F$3:$F$200,"Yes" ). When this formula looks at Column C, it correctly counts each Suppliers Recordables. But here's the problem, Column C could have the same Supplier name entered at a later date further down Column C. So for example, Supplier A is the first entry and the 30th entry in Column C and each entry has a corresponding YES entry in Column F. So the formula =COUNTIFS($C$3:$C$200,C4,$F$3:$F$200,"Yes" ) correctly counts 2 Recordables. However, when I drag the form down to the second occurrence for Supplier in row 30, it counts 2 again, thus overstating the total for Supplier A by 2 Recordables. I would like this 2nd occurrence and any additional occurrences for Supplier A to be blank cells. I only need to count the total once. Any additional help would be greatly appreciated!

 

[AhoyNC]

Try:

Excel Workbook
	C	D	E	F	G	H
2	Supplier
3	Sup1			yes		2
4	Sup2			no		1
5	Sup3			yes		2
6	Sup1			yes
7	Sup5			no		0
8	Sup2			yes
9	Sup3			yes
Sheet

 

[GERMAN GONZALEZ]

AhoyNC,

Thank you very much for your help! The two formulas worked flawlessly! I can't thank you enough!

All the Best,

Bruce

 

[AhoyNC]

You're welcome. Glad that worked for you. Thanks for the feedback.

 

[GERMAN GONZALEZ]

Not at all! You do awesome work! So glad you saw my posting and came to the rescue!