countif / index / match

Question

Hi&nbsp;Twifoo&nbsp;&nbsp;Are you able to help me with a formula? I am having trouble with countif/index/match..&nbsp;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)&nbsp;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")&nbsp;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!

twifoo · Answer

El1-_321&nbsp;Let me rephrase your requirement to this:&nbsp;&nbsp;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".&nbsp;&nbsp;The foregoing requirement translates to this formula:&nbsp;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 · Answer

Thank you&nbsp;Twifoo I understand the logic, but this formula doesn't seem to be working in excel..&nbsp;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'&nbsp;&nbsp;&nbsp;

twifoo · Answer

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

el1-_321 · Answer

Thank you&nbsp;Twifoo&nbsp;&nbsp;I have defined the ranges and am using the following formula but am still returning with a #N/A&nbsp;=IF(SUMPRODUCT((PLOD1=C1)*(PLOD2=B2)),SUMPRODUCT((PLOD1=C1)*(PLOD2=B2)),"No Data")&nbsp;Are my offsets correct? Im not sure about PLOD1 given im looking at a number of columns &nbsp;PLOD1&nbsp;=OFFSET('+PLOD'!$H$2,0,0,COUNTA('+PLOD'!$H:$AX),1)&nbsp;PLOD2&nbsp; =OFFSET('+PLOD'!$B$2,0,0,COUNTA('+PLOD'!$B:$B),1)&nbsp;

twifoo · Answer

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

Forum Discussion

countif / index / match

6 Replies

Resources