countif / index / match

[El1-_321]

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?

Thank you in advance!

[#2]

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

[#3]

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'

[#4]

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

[#5]

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)

[#6]

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

[#7]

@El1-_321 

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