Text count with various criteria

mballantyne

New Member
Joined
Oct 24, 2006
Messages
6
I would like to count text using two different criteria. Ex: In the following example spreadsheet I would like a count of how many times John was "in".
Book2
BCDEF
3
4Johnin
5Johnout
6Johnin
7Johnin
8Johnloa
9Johnin
10Johnout
11Janeloa
12Janeloa
13Janein
14Janein
15Mikeout
16Mikein
17Mikeloa
18Mikein
Sheet1
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Add Collumn E with formula
=CONCATENATE(C4,D4) : Resulting in Johnin
THen put this formula wherever youwant =COUNTIF(E:E,"johnin")
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

How about:

Code:
=SUMPRODUCT(--($C$4:$C$18="John"),--($D$4:$D$18="in"))

The "john" and "in" can also be cell references holding these values.
 

mballantyne

New Member
Joined
Oct 24, 2006
Messages
6
=SUMPRODUCT(--($C$4:$C$18="John"),--($D$4:$D$18="in"))

Would have worked fine except I gave a poor example. Replace the info in column D with dates. So what I would like is to find count how many times John's name appears on a specified date.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Code:
=SUMPRODUCT(--($C$4:$C$18="John"),--($D$4:$D$18=E1))

Where E1 holds the specific date you are referring to (amend as appropriate).
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Couldd you post an image of your actual data (ie with the dates) and also your exact formula and we'll investigate what the problem is.
 

mballantyne

New Member
Joined
Oct 24, 2006
Messages
6
Resource Pool Errors 24-10-20061.xls
EFGHIJK
2757259210/9/200610009737MissingBCRISOrderEBCCSOrderQError4#VALUE!
3830004610/9/200610005988MissingBCRISOrderEBCCSOrderQError1
4830004610/9/200610005988MissingBCRISOrderEBCCSOrderQError1
5628574510/10/200610012217BCRISCancelledEBCCSOrderQError4
6753796710/10/200610012538BCRISCancelledEBCCSOQError504
7812408910/10/200610123249BCRISCancelledEBCCSOQError504
8812408910/10/200610123249BCRISCancelledEBCCSOrderQError4
9628574510/10/200610012217BCRISCancelledEBCCSOQError504
10628437010/10/200610011662MissingBCRISOrderEBCCSOrderQError4
11706270910/10/200610079902MissingBCRISOrderEBCCSOrderQError4
12727839110/10/200610010099MissingBCRISOrderEBCCSOrderQError4
13759169310/10/200610010746MissingBCRISOrderEBCCSOrderQError4
HSC
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Right. First off, you can't use different sized ranges ranges in the Sumproduct (so the second argument ranges D:F won't work). We're only concerned with the date column, so that's OK - we can just use column F.

Secondly, (and this is the reason why I said to use a range reference ratrher than a hardcoded value) you need to surround the date in quotes for it to work.

Now, with the above in mind, give the following a try:

Code:
=SUMPRODUCT(--($H$2:$H$1000="Missing bcris order"),--($F$2:$F$1000="10/10/2006"))

Hopefully, this will now work. If you still have problems, try using a reference for the date criteria. Here you could use:

Code:
=SUMPRODUCT(--($H$2:$H$1000="Missing bcris order"),--($F$2:$F$1000=F5))

Hopefully, one of the above is going to work!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,993
Messages
5,545,377
Members
410,679
Latest member
rolandbianco
Top