CSE ARray formula not working in the top row

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
I've inherited a column with formula that goes through two columns of unsorted data (from a PowerQuery) and tries to get values for them to use on SUMIFS.

The formula is :-
=IFERROR(INDEX('Consol 2'!$H$7:$H$2002,MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002)+('Consol 2'!$D$7:$D$2002<>$B6)+('Consol 2'!$E$7:$E$2002<>Supplier),0)),"")

There's a Supplier picked from a drop-down list in the cell called 'Supplier'.
Column B has a service in which picks up correctly and Consol 2 column D has that service in.
Column H has a description in which can be blank.

On the first row, if the description has a blank in, the formula above returns a 0 - which the SUMIFS can't pick up on as the SUMIFS on the description is blank.
On subsequent rows, if the description has a blank in, the formula above returns a blank - which the SUMIFS can pick up on as the SUMIFS on the description is blank.

I'm sure it's something to do with the MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002) as the value in C5 is a column header that will never appear in the list of descriptions, so just returns ar array of 0's, but subsequent rows e.g. MATCH(0,COUNTIF($C$5:$C6,'Consol 2'!$H$7:$H$2002) will be>0

Unfortunately I inherited this and don't have time for a rewrite as it's a huge model that will ultimately be maintained by the people who wrote it in the first place.

TIA
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If the relevant cells in column H are actually blank, then that formula should be returning 0 for all of them. Does it work if you append "" to the result - i.e.:

Excel Formula:
=IFERROR(INDEX('Consol 2'!$H$7:$H$2002,MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002)+('Consol 2'!$D$7:$D$2002<>$B6)+('Consol 2'!$E$7:$E$2002<>Supplier),0))&"","")
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You did array enter it?
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008

ADVERTISEMENT

Yes, selected the column, made the change, hit CSE and it kept {} round it.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
No, it needs array entering into the top cell only, then copy/fill down.
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008

ADVERTISEMENT

Cheers but no joy. I removed the array, copied it down so the C$5:C5 filled out correctly, but then when I selected it all to do CSE it copied the C$5:C5 down, probably how it's all blank. I can't fill or copy down when it's a CSE. I'm in XL2016 if that makes any odds.
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
Right fixed it. I didn't thing could CSE a single cell but you can. I only ever used them once before and that was part of a competiton.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
There are two different kinds: formulas that process arrays and return a single result (this kind), and formulas that return an array of results - those are the ones that you array enter into multiple cells at once.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,349
Messages
5,635,768
Members
416,879
Latest member
Excel_Newbie4980

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
Top