SOLVED

Excel on COUNTIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-3133648%22%20slang%3D%22en-US%22%3EExcel%20on%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3133648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.jpg%22%20style%3D%22width%3A%20899px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346019iF861872259C8E1B0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.jpg%22%20alt%3D%22Untitled.jpg%22%20%2F%3E%3C%2FSPAN%3ECan%20anyone%20help%20with%20what%20is%20wrong%20in%20this%20formula%3CBR%20%2F%3E%3DCOUNTIFS(B2%3AB5%2C%22%26gt%3B%3DEOMONTH(TODAY()%2C-2)%20%2B1%22%2CC2%3AC5%2C%22N%2FA%22%2CD2%3AD5%2C%22%26lt%3B%3DEOMONTH(TODAY()%2C-2)%20%2B28%22)%3CBR%20%2F%3Eit's%20return%20value%20is%20just%20zero.%20please%20help%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3133648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3133748%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20on%20COUNTIFS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3133748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1300143%22%20target%3D%22_blank%22%3E%40donchips90%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlace%20the%20expressions%20using%20EOMONTH%20outside%20the%20quotes%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(B2%3AB5%2C%22%26gt%3B%3D%22%26amp%3BEOMONTH(TODAY()%2C-2)%20%2B1%2CC2%3AC5%2C%22N%2FA%22%2CD2%3AD5%2C%22%26lt%3B%3D%22%26amp%3BEOMONTH(TODAY()%2C-2)%20%2B28)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Untitled.jpgCan anyone help with what is wrong in this formula
=COUNTIFS(B2:B5,">=EOMONTH(TODAY(),-2) +1",C2:C5,"N/A",D2:D5,"<=EOMONTH(TODAY(),-2) +28")
it's return value is just zero. please help me.

3 Replies
best response confirmed by donchips90 (New Contributor)
Solution

@donchips90 

Place the expressions using EOMONTH outside the quotes:

 

=COUNTIFS(B2:B5,">="&EOMONTH(TODAY(),-2) +1,C2:C5,"N/A",D2:D5,"<="&EOMONTH(TODAY(),-2) +28)

@Hans Vogelaar thanks for the solution it has helped i appreciate.

but i have failed to understand again why still it fails to from this data i have attached excel workbook link. please try to see if you can help. or if anyone can, please.data13.jpghttps://1drv.ms/x/s!AtpPCuFXM6F2jDNkzxyxkGZN4WjD?e=9lLVxd 

@donchips90 

The values in the DATES, DATES2 and DATES3 columns look like dates but they are text values.

To correct this:

  • Select the DATES column.
  • On the Data tab of the ribbon, click Text to Columns.
  • Click Next > twice.
  • Select Date, and select DMY from the drop down list next to it.
  • Click Finish.
  • Repeat for the other two date columns.

See the attached version.