COUNTIF with multiple criteria

Marcia · #1 02-16-2019, 11:06 PM

Hi, I have a formula counting values with multiple criteria. It returns the correct value but the formula is quite looong. Is there a way to shorten it? I tried adding OR to remove the repetitive "REMARKS" but got error messages.

Code:

=COUNTIFS(SCHOOL_ID,$C$4,TRACK_STRAND,$F13,SEMESTER,$AD$4,SEX,J$11,DATE_ENROLLED,"<="&$AC$6,REMARKS,"<>"&"DRP",REMARKS,"<>"&"T/O",REMARKS,"<>"&"DIED",REMARKS,"<>"&"N/E",REMARKS,"<>"&"S/O")

Thank you.

ArviLaanemets · #2 02-17-2019, 01:41 AM

Add a (hidden) column, where you calculate remark group for every entry depending on what is entered as remark. Then you can use this column as last condition.
Of-course this works only, when a remark belongs to single group. I suspect you may want to filter on specific strings in Remark field. When this is case, you have to make the hidden column dynamic based on come general counting criteria you/user select(s) on worksheet.

Marcia · #3 02-17-2019, 05:12 AM

Quote:

Originally Posted by ArviLaanemets

I suspect you may want to filter on specific strings in Remark field. When this is case, you have to make the hidden column dynamic based on come general counting criteria you/user select(s) on worksheet.

You're correct Arvi, I want the countif formula to include all remarks except "DRP", "T/O", "DIED", "N/E" and "S/O".

Marcia · #4 02-17-2019, 05:19 AM

Quote:

Originally Posted by ArviLaanemets

I suspect you may want to filter on specific strings in Remark field. When this is case, you have to make the hidden column dynamic based on come general counting criteria you/user select(s) on worksheet.

You're correct Arvi, the formula counts all text strings under the REMARKS column except "DRP", "T/O", "DIED", "N/E" and "S/O".
Ooopps, it ais server error so I retyped my reply, how do I delete this?

ArviLaanemets · #5 02-17-2019, 07:54 AM

Do you mean all except when [@Remark] is "DRP" OR "T/O" OR "DIED", "N/E" OR "S/O"?
Or you mean all except when [@Remark] contains "DRP" OR "T/O" OR "DIED", "N/E" OR "S/O"?

Depending on your answer, the formula in additional column (e.g. RemarkGroup) in Table tSource will be:

(I assume you have defined the data table as Table (e.g. tSource). Otherwise you have to edit the formulas to use regular worksheet references. And I assume the final formula doesn't belong into Table tSource.)

Code:

= ([@Remark] = "DRP") + ([@Remark] = "T/O")+ ([@Remark] = "DIED")+ ([@Remark] = "N/E")+ ([@Remark] = "S/O")

or

Code:

= (Find("DRP",[@Remark]) >0) + (Find("T/O",[@Remark]) >0)+ (Find("DIED",[@Remark]) >0)+ (Find("N/E",[@Remark]) >0)+ (Find("S/O",[@Remark]) >0)

The final formula will be

Code:

=COUNTIFS(tSource[SCHOOL_ID],$C$4,tSource[TRACK_STRAND],$F13,tsource[SEMESTER],$AD$4,tSource[SEX],J$11,tSource[DATE_ENROLLED],"<="&$AC$6,tSource[RemarkGroup],0)

Marcia · #6 02-17-2019, 08:16 AM

Thank you again Arvi, I will try the first and third codes tomorrow.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Use countif across multiple tabs for a column	marconexcel	Excel	6	02-21-2017 05:38 AM
COUNTIF [criteria] + give %	Tomas4	Excel	2	12-20-2016 10:28 AM
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.	phillipsdp	Excel	1	11-09-2016 12:53 AM
Copy Multiple Rows to new workbook when multiple criteria is met.	flds	Excel Programming	5	09-30-2014 09:58 AM
Countif with 2 criteria	ibrahimaa	Excel	3	05-23-2011 11:23 AM