SOLVED
Home

TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

%3CLINGO-SUB%20id%3D%22lingo-sub-830951%22%20slang%3D%22en-US%22%3ETO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830951%22%20slang%3D%22en-US%22%3E%3CP%3ETO%20COUNT%20CELLS%20WITH%20TWO%20CRITERIA.%3C%2FP%3E%3CP%3ETHERE%20ARE%20TWO%20CELLS%20WITH%20NUMBERS%20AS%20A%20VALUES.%3C%2FP%3E%3CP%3EA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%3C%2FP%3E%3CP%3E80%26nbsp%3B%20%26nbsp%3B85%3C%2FP%3E%3CP%3E78%26nbsp%3B%20%26nbsp%3B82%3C%2FP%3E%3CP%3E77%26nbsp%3B%20%26nbsp%3B83%3C%2FP%3E%3CP%3E89%26nbsp%3B%20%26nbsp%3B84%3C%2FP%3E%3CP%3E90%26nbsp%3B%20%26nbsp%3B92%3C%2FP%3E%3CP%3EI%20WOULD%20LIKE%20TO%20COUNT%20IF%20THE%20VALUE%20IS%20LESS%20THAN%20%2280%22%20IN%20THEFIRST%20COLUMN%20AND%20THE%20DIFFERENCE%20BETWEEN%20TWO%20COLUMN%20IS%20LESS%20THAN%20%225%22.%3C%2FP%3E%3CP%3E1ST%20CRITERIA%20CAN%20BE%20ACHIEVED%20BY%20USING%20%22%26lt%3B80%22.%20BUT%20I%20COULDN'T%20ACHIEVE%202ND%20CRITERIA.%20PLEASE%20HELP%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-830951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830966%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400963%22%20target%3D%22_blank%22%3E%40MURALI1408%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20the%20%3CSTRONG%3ESUMPRODUCT%3C%2FSTRONG%3EFunction%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20numbers%20are%20in%20the%20range%20A1%3AB10%2C%20then%20try%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT((A1%3AA10%26lt%3B%26gt%3B%22%22)*(A1%3AA10%26lt%3B80)*(ABS(A1%3AA10-B1%3AB10)%26lt%3B5))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdjust%20the%20ranges%20in%20the%20formula%20as%20per%20your%20requirement%20but%20don't%20refer%20the%20whole%20column%20in%20the%20formula%20as%20it%20would%20take%20a%20long%20time%20to%20calculate%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830967%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400963%22%20target%3D%22_blank%22%3E%40MURALI1408%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT((A1%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%26lt%3B80)*(ABS(A1%3AINDEX(A%3AA%2CCOUNTA(A%3AA))-B1%3AINDEX(B%3AB%2CCOUNTA(A%3AA)))%26lt%3B5))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eassuming%20your%20data%20starts%20from%20first%20row%20of%20the%20sheet.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESame%20with%20static%20range%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT((A1%3AA5%26lt%3B80)*(ABS(A1%3AA5-B1%3AB5)%26lt%3B5))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-830985%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-830985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20included%20the%20criteria%20(A1%3AA10%26lt%3B%26gt%3B%22%22)%20to%20exclude%20the%20blank%20cells%20in%20column%20A%2C%20if%20any%2C%20in%20the%20range%20A1%3AA10%20otherwise%20the%20formula%20would%20return%20the%20wrong%20output.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831017%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eyes%2C%20I%20skipped%20that%20intentionally%20using%20dynamic%20range%2C%20otherwise%20it%20won't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831038%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831038%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EMr.Sergei%2C%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3Ethanks....but%20I%20want%20the%20count%20of%20columns%20matching%20these%20two%20criteria%5Bconditions%5D.....%3C%2FDIV%3E%3CDIV%3Ewith%20%3DCOUNTIF(A1%3AA5%2C%26lt%3B80)%2C%20I%20got%20the%20count%20correct%3C%2FDIV%3E%3CDIV%3Ebut%20for%202nd%20condition%2C%5Bdifference%20of%20A1%20%26amp%3B%20B1%20is%20less%20than%20%225%22)%20couldn't%20make.....%3C%2FDIV%3E%3CDIV%3Eplease%20help%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831046%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EMr.Sergei%2C%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3Ethanks....but%20I%20want%20the%20count%20of%20columns%20matching%20these%20two%20criteria%5Bconditions%5D.....%3C%2FDIV%3E%3CDIV%3Ewith%20%3DCOUNTIF(A1%3AA5%2C%26lt%3B80)%2C%20I%20got%20the%20count%20correct%3C%2FDIV%3E%3CDIV%3Ebut%20for%202nd%20condition%2C%5Bdifference%20of%20A1%20%26amp%3B%20B1%20is%20less%20than%20%225%22)%20couldn't%20make.....%3C%2FDIV%3E%3CDIV%3Eplease%20help%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-831308%22%20slang%3D%22en-US%22%3ERe%3A%20TO%20COUNT%20CELLS%20WITH%20MULTIPLE%20CRITERIA%20WITH%20COUNTIF%20FUNCTION.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400963%22%20target%3D%22_blank%22%3E%40MURALI1408%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20sample%20there%20is%20only%20one%20such%20combination%20and%20the%20formula%20gives%20correct%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20363px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129369i618552CED9ED93B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20what%20exactly%20doesn't%20work%2C%20which%20result%20do%20you%20expect%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
MURALI1408
New Contributor

TO COUNT CELLS WITH TWO CRITERIA.

THERE ARE TWO CELLS WITH NUMBERS AS A VALUES.

A     B

80   85

78   82

77   83

89   84

90   92

I WOULD LIKE TO COUNT IF THE VALUE IS LESS THAN "80" IN THEFIRST COLUMN AND THE DIFFERENCE BETWEEN TWO COLUMN IS LESS THAN "5".

1ST CRITERIA CAN BE ACHIEVED BY USING "<80". BUT I COULDN'T ACHIEVE 2ND CRITERIA. PLEASE HELP

7 Replies
Solution

@MURALI1408 

 

You may try the SUMPRODUCT Function like this...

 

Assuming your numbers are in the range A1:B10, then try this

 

=SUMPRODUCT((A1:A10<>"")*(A1:A10<80)*(ABS(A1:A10-B1:B10)<5))

 

Adjust the ranges in the formula as per your requirement but don't refer the whole column in the formula as it would take a long time to calculate it.

@MURALI1408 

As variant that could be

=SUMPRODUCT((A1:INDEX(A:A,COUNTA(A:A))<80)*(ABS(A1:INDEX(A:A,COUNTA(A:A))-B1:INDEX(B:B,COUNTA(A:A)))<5))

assuming your data starts from first row of the sheet. 

Same with static range

=SUMPRODUCT((A1:A5<80)*(ABS(A1:A5-B1:B5)<5))

@Sergei Baklan 

I included the criteria (A1:A10<>"") to exclude the blank cells in column A, if any, in the range A1:A10 otherwise the formula would return the wrong output.

@Subodh_Tiwari_sktneer 

yes, I skipped that intentionally using dynamic range, otherwise it won't work.

Mr.Sergei,

thanks....but I want the count of columns matching these two criteria[conditions].....
with =COUNTIF(A1:A5,<80), I got the count correct
but for 2nd condition,[difference of A1 & B1 is less than "5") couldn't make.....
please help

Mr.Sergei,

thanks....but I want the count of columns matching these two criteria[conditions].....
with =COUNTIF(A1:A5,<80), I got the count correct
but for 2nd condition,[difference of A1 & B1 is less than "5") couldn't make.....
please help

@MURALI1408 

In your sample there is only one such combination and the formula gives correct result

image.png

Could you please clarify what exactly doesn't work, which result do you expect?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies