Forum Discussion

Copper Contributor

Jul 23, 2019

countif / index / match

Hi Twifoo Are you able to help me with a formula? I am having trouble with countif/index/match.. I need to look at a range of data (+PLOD'!$H$2:$AX$1048576) and count the number of times a f...

excel

El1-_321

Copper Contributor

Jul 24, 2019

Thank you Twifoo I understand the logic, but this formula doesn't seem to be working in excel..

I am getting an error stating 'Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated'

Twifoo

Silver Contributor

Jul 25, 2019

Instead of using direct references, define them as dynamic ranges. Thereafter, use those defined names in the formula I suggested.

El1-_321
Copper Contributor
Jul 26, 2019
Thank you Twifoo

I have defined the ranges and am using the following formula but am still returning with a #N/A

=IF(SUMPRODUCT((PLOD1=C1)*(PLOD2=B2)),SUMPRODUCT((PLOD1=C1)*(PLOD2=B2)),"No Data")

Are my offsets correct? Im not sure about PLOD1 given im looking at a number of columns

PLOD1 =OFFSET('+PLOD'!$H$2,0,0,COUNTA('+PLOD'!$H:$AX),1)

PLOD2 =OFFSET('+PLOD'!$B$2,0,0,COUNTA('+PLOD'!$B:$B),1)
- Twifoo
  Silver Contributor
  Jul 26, 2019
  El1-_321
  OFFSET is a volatile function. Instead, use INDEX to define the names like these:
  
  PLOD1='+PLOD'!$H$2:INDEX('+PLOD'!$H$2:$AX$1048576,COUNTA('+PLOD'!$H$2:$H$1048576),COUNTA('+PLOD'!$H$2:$AX$2))
  
  PLOD2='+PLOD'!$B$2:INDEX('+PLOD'!$B$2:$B$1048576,COUNTA('+PLOD'!$B$2:$B$1048576))
  
  Thereafter, use the foregoing names in the formula like this:
  IF(SUMPRODUCT((PLOD1=C1)*
  (PLOD2='PLOD SUM'!B2)),
  SUMPRODUCT((PLOD1=C1)*
  (PLOD2='PLOD SUM'!B2)),
  "No Data")