SUMPRODUCT/COUNTIFS array formula works perfectly, but it's slow and killing my processors!!

[dougbert]

Hi all,

I found an array formula that I adapted into my Excel 2013 workbook. My version entered as an array formula reads as follows:

{=SUMPRODUCT(IFERROR(($B$2:$B$1667&$F$2:$F$1667=$B2&$F2)/COUNTIFS($S$2:$S$1667,$S$2:$S$1667,$B$2:$B$1667,$B2,$F$2:$F$1667,$F2),0),$S$2:$S$1667)}

It works perfectly for what I need to accomplish. Unfortunately, it takes "forever" to calculate down a column. If I let it run several minutes and then ctrl-break, I’ll discover it’s only finished about half of the calculations. On top of that, this array formula seems to make my worksheet constantly recalculate the sheet. So, I end up setting Calculation Options to Manual just so I can perform other maintenance on this sheet.

Workbook Structure:

Columns B and F contain the criteria I need to match. Column S contains larger integers. However, the collection of integers in column S often times contains identical values. The formula above finds the set of unique integers within column S that also satisfy the criteria in B and F; then sums the unique integers.

Hopefully, someone can conceptualize my description. If not, I’ll be happy to supply a sample worksheet that illustrates if needed.

So, I’m looking to your collective brilliant minds to offer suggestions for a much faster running and efficient formula that will accomplish the same as the formula above.

Thanks for any suggestions!

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$1667=$B2,IF($F$2:$F$1667=$F2,
  MATCH($S$2:$S$1667,$S$2:$S$1667,0))),
  ROW($S$2:$S$1667)-ROW($S$2)+1),S$2:$S$1667))

Even better... Assuming that Sheet1 houses the data, define first Ivec (from integer vector) using Formulas | Name Manager as referring to:

=ROW(Sheet1!$S$2:$S$1667)-ROW(Sheet1!$S$2)+1

then invoke:

=SUM(IF(FREQUENCY(IF($B$2:$B$1667=$B2,IF($F$2:$F$1667=$F2,
  MATCH($S$2:$S$1667,$S$2:$S$1667,0))),Ivec),S$2:$S$1667))

still to be confirmed with control+shift+enter and copied down.

 

[Fazza]

hi

What about a non-formula solution? Such as a pivot table - which should readily handle huge amounts of data. If that is of interest, please post some sample data inputs & corresponding results.

regards

 

[dougbert]

Aladin: thank you for your suggestion. I had also tried something similar previously, but apparently didn't get my formula correct. I ended up using your 2nd method. While the calculation still doesn't occur at lightning speed, it's at least a factor of 10 better than what I had before. Thanks for your efforts!

Fazza: you couldn’t have known this from the information I provided, but this formula is one of many that are in calculated columns immediately to the right of my actual data. I needed to do it this way, so that I could create a pivot table from this data worksheet. The reason I must do it this way is because using a calculated field in the pivot table would not return the correct results of the formula I need to ultimately replicate. Essentially, the formula is a count divided by this unique set of integers multiplied by another factor. When you try to drag this calculated field to the Values pane, the choices of Sum, Average, Count, etc. won’t express the single value properly. Consequently, the resulting pivot table and pivot chart had no usable value.

Thanks for both of your replies!

 

[Aladin Akyurek]

Aladin: thank you for your suggestion. I had also tried something similar previously, but apparently didn't get my formula correct. I ended up using your 2nd method. While the calculation still doesn't occur at lightning speed, it's at least a factor of 10 better than what I had before. Thanks for your efforts!...

Let's try an additional improvement.

A1: Concat
T1: UTotal

A2, just enter and copy down:

=B2&"|"&F2

1.

T2, control+shift+enter and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$1667=$B2&"|"$F2,
  MATCH($S$2:$S$1667,$S$2:$S$1667,0)),Ivec),S$2:$S$1667))

This trades off some memory against speed.

2. Also, if B and F cells often repeat...

T2, control+shift+enter and copy down:

=IFERROR(INDEX($T$1:T1,MATCH($B2&"|"&F2,$A$1:A1,0)),
  SUM(IF(FREQUENCY(IF($A$2:$A$1667=$B2&"|"&$F2,
  MATCH($S$2:$S$1667,$S$2:$S$1667,0)),Ivec),S$2:$S$1667)))

 

[Fazza]

Aladin: thank you for your suggestion. I had also tried something similar previously, but apparently didn't get my formula correct. I ended up using your 2nd method. While the calculation still doesn't occur at lightning speed, it's at least a factor of 10 better than what I had before. Thanks for your efforts!

Fazza: you couldn’t have known this from the information I provided, but this formula is one of many that are in calculated columns immediately to the right of my actual data. I needed to do it this way, so that I could create a pivot table from this data worksheet. The reason I must do it this way is because using a calculated field in the pivot table would not return the correct results of the formula I need to ultimately replicate. Essentially, the formula is a count divided by this unique set of integers multiplied by another factor. When you try to drag this calculated field to the Values pane, the choices of Sum, Average, Count, etc. won’t express the single value properly. Consequently, the resulting pivot table and pivot chart had no usable value.

Thanks for both of your replies!

What you want may be possible without any formulas at all - no calculated columns. The complexity may be too much, so it may not be possible too. Calculated fields in the normal Excel interface are limited but other approaches are superior.

 

[Fazza]

PS

An old thread with links to some examples www.mrexcel.com/forum/excel-questions/603272-using-countifs-sumifs-pivot-tables.html

Google will find MANY more similar posts (from me on Mr Excel)

regards

 

[dougbert]

Thank you both again for the additional resources and alternate calculation method, Aladin. I may try that with next month's report.

 

[Aladin Akyurek]

Thank you both again for the additional resources and alternate calculation method, Aladin. I may try that with next month's report.

Right. I'm always curious about the results timewise.