 Highlighted

# 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 figure appears (\$C\$1) if the date ('PLOD Sum'!B2) appears in this field ('+PLOD'!\$B\$2:\$B\$1048576)

The formula I am using is this:

=IFERROR(COUNTIF(INDEX('+PLOD'!\$H\$2:\$AX\$1048576,MATCH('PLOD Sum'!B2,'+PLOD'!\$B\$2:\$B\$1048576,0),),\$C\$1),"No Data")

The issue I am having is there is more than one date row with relevant figures to count, so when I use the above formula it works but is only counting the first row with the relevant date instead of the number of rows with the relevant date... I hope this makes sense?

6 Replies
Highlighted

# Re: countif / index / match

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")

Highlighted

# Re: countif / index / match

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'

Highlighted

# Re: countif / index / match

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

# Re: countif / index / match

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)

Highlighted

# Re: countif / index / match

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")

Highlighted

# Re: countif / index / match

Recently, I eschewed the repetition of ISNUMBER-FIND in my reply here:

https://techcommunity.microsoft.com/t5/Excel/extract-unique-values-matching-a-text-string/m-p/792713...

Similarly, the repetition of SUMPRODUCT in my suggested formula could also be eschewed like this:

IFERROR(1/(1/(

SUMPRODUCT((PLOD1=C1)*

(PLOD2='PLOD SUM'!B2)))),

"No Data")