Home

countif / index / match

%3CLINGO-SUB%20id%3D%22lingo-sub-768921%22%20slang%3D%22en-US%22%3Ecountif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-768921%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20able%20to%20help%20me%20with%20a%20formula%3F%20I%20am%20having%20trouble%20with%20countif%2Findex%2Fmatch..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20look%20at%20a%20range%20of%20data%20(%3CFONT%3E%2BPLOD'!%24H%242%3A%24AX%241048576%3C%2FFONT%3E)%20and%20count%20the%20number%20of%20times%20a%20figure%20appears%20(%3CFONT%3E%24C%241%3C%2FFONT%3E)%20if%20the%20date%20(%3CFONT%3E'PLOD%20Sum'!B2)%20appears%20in%20this%20field%20('%2BPLOD'!%24B%242%3A%24B%241048576)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThe%20formula%20I%20am%20using%20is%20this%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIFERROR(COUNTIF(INDEX('%2BPLOD'!%24H%242%3A%24AX%241048576%2CMATCH('PLOD%20Sum'!B2%2C'%2BPLOD'!%24B%242%3A%24B%241048576%2C0)%2C)%2C%24C%241)%2C%22No%20Data%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ET%3CFONT%3Ehe%20issue%20I%20am%20having%20is%20there%20is%20more%20than%20one%20date%20row%20with%20relevant%20figures%20to%20count%2C%20so%20when%20I%20use%20the%20above%20formula%20it%20works%20but%20is%20only%20counting%20the%20first%20row%20with%20the%20relevant%20date%20instead%20of%20the%20number%20of%20rows%20with%20the%20relevant%20date...%20I%20hope%20this%20makes%20sense%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-768921%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-769010%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-769010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323056%22%20target%3D%22_blank%22%3E%40El1-_321%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20rephrase%20your%20requirement%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ECOUNT%20the%20instances%20of%20the%20value%20in%20C1%20in%20the%20range%20'%2BPLOD'!H2%3AAX1048576%2C%20only%20in%20rows%20where%20the%20date%20in%20'PLOD%20SUM'!B2%20is%20found%20in%20'%2BPLOD'!B2%3AB1048576%3B%20otherwise%2C%20return%20%22No%20Data%22.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20foregoing%20requirement%20translates%20to%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIF(SUMPRODUCT(('%2BPLOD'!H2%3AAX1048576%3DC1)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('PLOD'!B2%3AB1048576%3D'PLOD%20SUM'!B2))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESUMPRODUCT(('%2BPLOD'!H2%3AAX1048576%3DC1)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('PLOD'!B2%3AB1048576%3D'PLOD%20SUM'!B2))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22No%20Data%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772607%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772607%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3EI%20understand%20the%20logic%2C%20but%20this%20formula%20doesn't%20seem%20to%20be%20working%20in%20excel..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20getting%20an%20error%20stating%20'Excel%20ran%20out%20of%20resources%20while%20attempting%20to%20calculate%20one%20or%20more%20formulas.%20As%20a%20result%2C%20these%20formulas%20cannot%20be%20evaluated'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772871%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772871%22%20slang%3D%22en-US%22%3EInstead%20of%20using%20direct%20references%2C%20define%20them%20as%20dynamic%20ranges.%20Thereafter%2C%20use%20those%20defined%20names%20in%20the%20formula%20I%20suggested.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774430%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774430%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20defined%20the%20ranges%20and%20am%20using%20the%20following%20formula%20but%20am%20still%20returning%20with%20a%20%23N%2FA%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(SUMPRODUCT((PLOD1%3DC1)*(PLOD2%3DB2))%2CSUMPRODUCT((PLOD1%3DC1)*(PLOD2%3DB2))%2C%22No%20Data%22)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EAre%20my%20offsets%20correct%3F%20Im%20not%20sure%20about%20PLOD1%20given%20im%20looking%20at%20a%20number%20of%20columns%20%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EPLOD1%26nbsp%3B%3C%2FFONT%3E%3CFONT%3E%3CFONT%3E%3DOFFSET('%2BPLOD'!%24H%242%2C0%2C0%2CCOUNTA('%2BPLOD'!%24H%3A%24AX)%2C1)%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EPLOD2%26nbsp%3B%20%3DOFFSET('%2BPLOD'!%24B%242%2C0%2C0%2CCOUNTA('%2BPLOD'!%24B%3A%24B)%2C1)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774584%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323056%22%20target%3D%22_blank%22%3E%40El1-_321%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOFFSET%3C%2FSTRONG%3Eis%20a%20volatile%20function.%20Instead%2C%20use%20%3CSTRONG%3EINDEX%3C%2FSTRONG%3Eto%20define%20the%20names%20like%20these%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPLOD1%3C%2FSTRONG%3E%3D'%2BPLOD'!%24H%242%3AINDEX('%2BPLOD'!%24H%242%3A%24AX%241048576%2CCOUNTA('%2BPLOD'!%24H%242%3A%24H%241048576)%2CCOUNTA('%2BPLOD'!%24H%242%3A%24AX%242))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EPLOD2%3C%2FSTRONG%3E%3D'%2BPLOD'!%24B%242%3AINDEX('%2BPLOD'!%24B%242%3A%24B%241048576%2CCOUNTA('%2BPLOD'!%24B%242%3A%24B%241048576))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThereafter%2C%20use%20the%20foregoing%20names%20in%20the%20formula%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIF(SUMPRODUCT((PLOD1%3DC1)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(PLOD2%3D'PLOD%20SUM'!B2))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESUMPRODUCT((PLOD1%3DC1)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(PLOD2%3D'PLOD%20SUM'!B2))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22No%20Data%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-794385%22%20slang%3D%22en-US%22%3ERe%3A%20countif%20%2F%20index%20%2F%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323056%22%20target%3D%22_blank%22%3E%40El1-_321%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERecently%2C%20I%20eschewed%20the%20repetition%20of%20ISNUMBER-FIND%20in%20my%20reply%20here%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fextract-unique-values-matching-a-text-string%2Fm-p%2F792713%23M37852%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2Fextract-unique-values-matching-a-text-string%2Fm-p%2F792713%23M37852%3C%2FA%3E%3C%2FP%3E%3CP%3ESimilarly%2C%20the%20repetition%20of%20SUMPRODUCT%20in%20my%20suggested%20formula%20could%20also%20be%20eschewed%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EIFERROR(1%2F(1%2F(%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESUMPRODUCT((PLOD1%3DC1)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E(PLOD2%3D'PLOD%20SUM'!B2))))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22No%20Data%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
El1-_321
Occasional Contributor

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!

6 Replies

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

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'

 

 

 

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

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)

 

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

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies