COUNTIF with two criteria

[gumbles]

Hi there,


I am trying to use the COUNTIF function to count issues only when the "issueopendate" range matches two criteria.


1. If it matches the number in cell "$O38" (e.g.32)

2. If the Text in the range "IssueQuestion" begins with a Q. (e.g. Q1.)


The purpose is to first divide the isses by week then divide them by question type. each question will start with either Q,S,C,D,P


The attempt I have made is this but with no success


=COUNTIF(IssueOpenDate,AND($O38,LEFT(IssueQuestion,1)="Q"))


Any Help Welcome,


Gumbles

 

[shrivallabha]

Hi gumbles,


What version of Excel you have?

 

[gumbles]

Hi Shrivallabha,


2007.

 

[shrivallabha]

Then please take a look at COUNTIFS function which would have construct like:

=COUNTIFS(IssueOpenDate,$O38,IssueQuestion,"Q*")

 

[gumbles]

Ok I have tried this formula in my spreadsheet but I dont think it is exactly what I need.


I think the problem is that I need to look for the second criteria (if the left most character is "Q")on the same line(s) as I found the first criteria (if it matches the value in $O38).


Is this possible?


Gumbles

 

[shrivallabha]

Care to post some sample data and expected result?

 

[jason]

i sounds like you'll need to use a nested AND function:


COUNTIF(Range,And(criteria1,criteria2))


a sample of the spreadsheet may be helpful as the layout of data can play a roll in your options.

 

[gumbles]

Sorry,


Inputs:


Week Opened Issues Question

32 Q1.

32 S1.

33 Q1.


Desired Outputs


Week Total Open "Q" Total Open "S"

32 1 1

33 1 0


I will make them cumulative later but that's not so difficult.


Gumbles

 

[gumbles]

That format didnt really come out right. Unfortunatly i cant upload a sample as my company firewall doesnt let me

 

[Debraj]

Hi Gumbles,


As you have mentioned, format is not the same as you type, still I wish I have decoded in correctly.

[pre]

Week	Total Open "Q"	Total Open "S"
32	1	        1
33	1	        0[/pre]
Please can you try the below formula in E1, where data are from A1 to B4 and above format is in D1 to F3

=COUNTIFS($A$2:$A$4,$D2,$B$2:$B$4,MID(E$1,FIND("""",E$1)+1,1)&"*")

make changes as required..


Regards,

Deb

 

[shrivallabha]

Deb has given you what I wanted to suggest. For simplicity, I'd use:

for Q

=COUNTIFS($A$2:$A$4,$D2,$B$2:$B$4,"Q*")

for S

=COUNTIFS($A$2:$A$4,$D2,$B$2:$B$4,"S*")