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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Add Collumn E with formula
=CONCATENATE(C4,D4) : Resulting in Johnin
THen put this formula wherever youwant =COUNTIF(E:E,"johnin")
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top