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

Twifoo

Silver Contributor

Jul 23, 2019

El1-_321

Let me rephrase your requirement to this:

COUNT the instances of the value in C1 in the range '+PLOD'!H2:AX1048576, only in rows where the date in 'PLOD SUM'!B2 is found in '+PLOD'!B2:B1048576; otherwise, return "No Data".

The foregoing requirement translates to this formula:

IF(SUMPRODUCT(('+PLOD'!H2:AX1048576=C1)*

('PLOD'!B2:B1048576='PLOD SUM'!B2)),

SUMPRODUCT(('+PLOD'!H2:AX1048576=C1)*

('PLOD'!B2:B1048576='PLOD SUM'!B2)),

"No Data")

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)