countifs in Excel 365

%3CLINGO-SUB%20id%3D%22lingo-sub-2766979%22%20slang%3D%22en-US%22%3Ecountifs%20in%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2766979%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hive%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20struggling%20with%20the%20below%20%22countifs%22%20statements.%26nbsp%3B%20this%20is%20a%20simplified%20version%20of%20my%20actual%20formula%2C%20but%20I'm%20getting%20the%20same%20weird%20output.%3C%2FP%3E%3CP%3EBasically%2C%20I'm%20trying%20to%20count%20up%20the%20number%20of%20dates%20in%20column%20B%20that%20are%20greater%20than%20or%20equal%20to%20dates%20in%20column%20A%20by%20month(defined%20in%20cells%20D1%20%26amp%3B%20E1)%2C%20and%20where%20column%20B%20is%20not%20blank.%3C%2FP%3E%3CP%3EThis%20is%20my%20formula%20in%20cell%20D2%3A%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24A%242%3A%24A%245%2C%22%26gt%3B%3D%22%26amp%3BD1%2C%24A%242%3A%24A%245%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(D1%2C0)%2C%24B%242%3A%24B%245%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22%2C%24B%242%3A%24B%245%2C%22%26lt%3B%3D%22%26amp%3B%40%24A%242%3A%24A%245)%3C%2FP%3E%3CP%3Eand%20in%20E2%3A%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24A%242%3A%24A%245%2C%22%26gt%3B%3D%22%26amp%3BE1%2C%24A%242%3A%24A%245%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(E1%2C0)%2C%24B%242%3A%24B%245%2C%22%26lt%3B%26gt%3B%22%26amp%3B%22%22%2C%24B%242%3A%24B%245%2C%22%26lt%3B%3D%22%26amp%3B%40%24A%242%3A%24A%245)%3C%2FP%3E%3CTABLE%20width%3D%22353%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2275%22%3EPlanned%3C%2FTD%3E%3CTD%20width%3D%2275%22%3EActual%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2275%22%3ESep-21%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EOct-21%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F09%2F2021%3C%2FTD%3E%3CTD%3E31%2F08%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E02%2F09%2F2021%3C%2FTD%3E%3CTD%3E01%2F09%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E03%2F09%2F2021%3C%2FTD%3E%3CTD%3E02%2F09%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E01%2F10%2F2021%3C%2FTD%3E%3CTD%3E30%2F09%2F2021%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eusing%20the%20current%20values%20in%20column%20B%2C%20my%20actual%20answers%20should%20be%203%20for%20September%20and%201%20for%20October.%26nbsp%3B%20I%20think%20this%20may%20be%20with%20the%20new%20spill%20feature%20in%20excel%20365%20as%20I%20don't%20think%20I've%20had%20this%20problem%20before.%26nbsp%3B%20The%20%22%40%22%20was%20added%20by%20excel%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20logic%20in%20the%20formula%20is%3A%3C%2FP%3E%3CP%3Ecount%20where%20cells%20in%20range%20%24A%242%3A%24A%245%20are%20greater%20than%20or%20equal%20to%20start%20of%20month%3C%2FP%3E%3CP%3EAND%3C%2FP%3E%3CP%3Ewhere%20cells%20in%20range%20%24A%242%3A%24A%245%20are%20less%20than%20or%20equal%20to%20end%20of%20month%3C%2FP%3E%3CP%3EAND%26nbsp%3B%3C%2FP%3E%3CP%3Ewhere%20cells%20in%20range%20%24B%242%3A%24B%245%20are%20not%20equal%20to%20%22%22%26nbsp%3B%3C%2FP%3E%3CP%3EAND%3C%2FP%3E%3CP%3Ewhere%20cells%20in%20range%26nbsp%3B%24B%242%3A%24B%245%20are%20less%20than%20or%20equal%20to%20their%20corresponding%20cells%20in%20range%26nbsp%3B%24A%242%3A%24A%245%20(eg%20B3%20%26lt%3B%3DA3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20round%20this%20problem%2C%20I've%20added%20a%20helper%20column%20with%20formulas%20to%20my%20workbook%2C%20but%20I'd%20rather%20not%20use%20that%20if%20I%20can%20solve%20this%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20much%20appreciated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2766979%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767111%22%20slang%3D%22en-US%22%3ERe%3A%20countifs%20in%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161045%22%20target%3D%22_blank%22%3E%40stephen1905%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20formula%20you%20use%26nbsp%3B%3CSPAN%3E%26amp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%40%3C%2FSTRONG%3E%3C%2FFONT%3E%24A%242%3A%24A%245%2C%20that%20means%20you%20compare%20with%20value%20in%20current%20row%20of%20A2%3AA5%2C%20i.e.%20all%20less%20than%20or%20equal%20to%2001%20Sep%20for%20the%20first%20row.%20It%20correctly%20returns%202.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2767132%22%20slang%3D%22en-US%22%3ERe%3A%20countifs%20in%20Excel%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2767132%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20quick%20response%20Sergei.%3CBR%20%2F%3EI%20don't%20understand%20though.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EWhat%20I%20was%20expecting%20to%20see%20with%20those%20values%20is%20a%20match%20for%20all%20rows%2C%20and%20therefore%20D2%20should%20be%20a%20total%20of%203%20and%20E2%20a%20total%20of%201.%20I%20want%20the%20total%20count%20of%20rows%20matching%20the%20criteria.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20better%20formula%20to%20use%20to%20achieve%20this%3F%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E
New Contributor

Hi Hive

 

I'm struggling with the below "countifs" statements.  this is a simplified version of my actual formula, but I'm getting the same weird output.

Basically, I'm trying to count up the number of dates in column B that are greater than or equal to dates in column A by month(defined in cells D1 & E1), and where column B is not blank.

This is my formula in cell D2:

=COUNTIFS($A$2:$A$5,">="&D1,$A$2:$A$5,"<="&EOMONTH(D1,0),$B$2:$B$5,"<>"&"",$B$2:$B$5,"<="&@$A$2:$A$5)

and in E2:

=COUNTIFS($A$2:$A$5,">="&E1,$A$2:$A$5,"<="&EOMONTH(E1,0),$B$2:$B$5,"<>"&"",$B$2:$B$5,"<="&@$A$2:$A$5)

PlannedActual Sep-21Oct-21
01/09/202131/08/2021 20
02/09/202101/09/2021   
03/09/202102/09/2021   
01/10/202130/09/2021   

using the current values in column B, my actual answers should be 3 for September and 1 for October.  I think this may be with the new spill feature in excel 365 as I don't think I've had this problem before.  The "@" was added by excel 

my logic in the formula is:

count where cells in range $A$2:$A$5 are greater than or equal to start of month

AND

where cells in range $A$2:$A$5 are less than or equal to end of month

AND 

where cells in range $B$2:$B$5 are not equal to "" 

AND

where cells in range $B$2:$B$5 are less than or equal to their corresponding cells in range $A$2:$A$5 (eg B3 <=A3)

 

To get round this problem, I've added a helper column with formulas to my workbook, but I'd rather not use that if I can solve this problem.

 

Any suggestions much appreciated

 

 

4 Replies

@stephen1905 

In formula you use &@$A$2:$A$5, that means you compare with value in current row of A2:A5, i.e. all less than or equal to 01 Sep for the first row. It correctly returns 2.

Thanks for the quick response Sergei.
I don't understand though.
What I was expecting to see with those values is a match for all rows, and therefore D2 should be a total of 3 and E2 a total of 1. I want the total count of rows matching the criteria.

Is there a better formula to use to achieve this?
Thanks

@stephen1905 

 

If you have the most recent version of Excel, then the dynamic array function FILTER can be used, in conjunction with COUNT (as this image shows)...  [You can use multiple criteria in FILTER as well]

mathetes_0-1632144889468.png

 

Here's a video that Microsoft released to explain FILTER and a couple other Dynamic Array functions. Well worth learning about.

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Thanks Mathetes

exactly what i wanted.
many thanks