# Video 00156 One Complex Formula or Helper Formulas?

There are many factors to consider when creating a solution in Excel. Are we building the solution for someone else with basic Excel skills? Should we use a complex formula in only one column to save space or use helper formulas to simplify the solution? Let’s take a look at this question from Mike Girvin’s ExcelIsFun YouTube channel.

## What’s The Requirement?

We need to count the number of ‘Sub Items’ per ‘Item’ (K123 has 4, H322 has 2, C983 has 6). We also need to lookup each ‘Item’ and return a text.

The ‘Description’ field should contain our formula solution. It shows the count of ‘Sub Items’ per ‘Item’ and also retrieves the ‘Item’ flower name.

## Can We Use The ‘Fill In The Blanks’ Trick?

If so this would make it easier but in this case let’s assume that we can’t change the layout of fields ‘Item’ and ‘Sub-item’.

## A Question That Can Make A BIG Difference!

Do we solve this using 1 formula or use helper columns?

If we try to use only 1 formula then it could become a very complex solution. What if you or someone else needs to slightly modify the formula a few weeks from now? How long would it take to audit the formula? If you build the solution for someone else wouldn’t you want them to understand the solution whenever possible? If not they could be stopping by your desk a lot!

## Single Formula Approach Examples

Example #1

=IF(A6=””,IF(COUNTIFS(A6:\$A\$20,”<>”)=0,ROWS(\$A\$6:\$A\$20)-MATCH(“zzzzz”,\$A\$6:\$A\$20),SUMPRODUCT(–(INDEX(\$A\$6:\$A\$20,MATCH(LOOKUP(“zzzzz”,A5:\$A\$6),\$A\$6:\$A\$20,0)):INDEX(A6:\$A\$20,MATCH(TRUE,INDEX(ISTEXT(A6:\$A\$20),),0))=0)))&” in “&VLOOKUP(LOOKUP(“zzzzz”,\$A\$6:A6),\$H\$6:\$I\$8,2,0),””)

It’s great that it’s only 1 formula but trying to explain this would be a big challenge.

Example #2

=IF(A6=””,IF(C5<>””,C5,IFERROR(MATCH(“?*”,A6:\$A\$20,0)-1,ROWS(A6:\$A\$20))&” in “&VLOOKUP(A5,\$H\$6:\$I\$8,2,0)),””)

The formula above is from Bill Szysz. It’s a much easier solution to audit/modify.

## Helper Formula Example

This is the solution I created. I broke the solution down into four steps:

1: Repeat Item: This helper column to repeat ‘Item’ makes it so much easier to solve =IF(A6=””,E5,A6)

2: Add countif: Counts occurrences of ‘ItemNo’ in step 1 above =COUNTIF(\$E\$6:\$E\$20,H6)

3: Prep number & text: Concatenate the count (do a -1 on the count) with the text =J6-1& ” in “&I6

4: Get Answer: If and Index Match solves it =IF(B6=””,””,INDEX(\$K\$6:\$K\$8,MATCH(E6,\$H\$6:\$H\$8,0)))

I created the video at least 1 month ago and it was easy to audit my solution as the steps are clearly labelled. Thanks me 🙂

Mike’s video and My video.

## Excel Files

Mike’s Excel file and here is my Excel file.