Countifs or sumproduct with Wild Cards?

eblake

Active Member
Joined
Aug 18, 2004
Messages
258
Hello all,
We have 5 columns in our worksheet that we want to check to look for specific data and return the number of rows returne, the problem is 2 of the columns that we need to check are for the same information, but one is embedded in an email address the other is just the information we are looking for.

We have tried to use sumproduct but it doesn't do wildcards and countifs doesnt want to do col arrays, tried to use an OR statement but that didn't work either. We discussed adding another column, but want to avoid that if possible (clutters up the report).
Code:
[RANGE=cls:xl2bb-100][XR][XH=cs:6]Excel 2010[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]1[/XH][XD=h:l]Week[/XD][XD=h:l]Date[/XD][XD=h:l]Contact_Type[/XD][XD=h:l]Name[/XD][XD=h:l]email[/XD][/XR][XR][XH]2[/XH][XD=h:r]1[/XD][XD=h:r]1/2/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l|c:0000ff]jz9999@emaildoman.com[/XD][/XR][XR][XH]3[/XH][XD=h:r]2[/XD][XD=h:r]1/10/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH]4[/XH][XD=h:r]3[/XD][XD=h:r]1/16/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH]5[/XH][XD=h:r]4[/XD][XD=h:r]1/25/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH]6[/XH][XD=h:r]12[/XD][XD=h:r]3/20/2013[/XD][XD=h:l]Person[/XD][XD=h:l]jz9999[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH]7[/XH][XD=h:r]16[/XD][XD=h:r]3/25/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH]8[/XH][XD=h:r]16[/XD][XD=h:r]4/18/2013[/XD][XD=h:l]Person[/XD][XD=h:l]John Zells[/XD][XD=h:l]jz9999@emaildoman.com[/XD][/XR][XR][XH=cs:6][RANGE][XR][XD]Sheet2[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

So what we need to do is pull the following count:
Week : 16
Contact Type: Person
Name: jz9999
Email contains: jz9999

We can get it to work with 3 of the 4 criteria, the sticking point is counting Name AND Email contains. I'm probably overthinking it and it's right in front of my eyes, but would appreciate any help.

thanks
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

The following is sample data and example results....

Excel Workbook
ABCDEFGHIJKLM
1WeekDateContact_TypeNameemail*WeekContact TypeNameEmail Contains*Count*
2101/04/2013PersonJohn Zellsjz9999@emaildoman.com*16Personjz9999jz9999*1*
3202/04/2013PersonJohn Zellsjz9999@emaildoman.com******1*
4303/04/2013PersonJohn Zellsjz9999@emaildoman.com********
5404/04/2013PersonJohn Zellsjz9999@emaildoman.com********
61205/04/2013Personjz9999jz9999@emaildoman.com********
71606/04/2013PersonJohn Zellsjz9999@emaildoman.com********
81607/04/2013Personjz9999jz9999@emaildoman.com********
9*************
Sheet3


Second Example....

Excel Workbook
ABCDEFGHIJKLM
1WeekDateContact_TypeNameemailWeekContact TypeNameEmail ContainsCount
2101/04/2013PersonJohn Zellsjz9999@emaildoman.com16Personjz9999jz99992
3202/04/2013PersonJohn Zellsjz9999@emaildoman.com2
4303/04/2013PersonJohn Zellsjz9999@emaildoman.com
5404/04/2013PersonJohn Zellsjz9999@emaildoman.com
61205/04/2013Personjz9999jz9999@emaildoman.com
71606/04/2013Personjz9999jz9999@emaildoman.com
81607/04/2013Personjz9999jz9999@emaildoman.com
9
Sheet3



You will need to change the cell references to suit your layout.

I hope that helps.

Ak
 

eblake

Active Member
Joined
Aug 18, 2004
Messages
258
Thank you for the quick response, they all work if you replace D7 and D8 with the ID, but not if the name is in there it returns 0.

We need to count how many rows have the ID in either Col D or in Col E. You'd think Countifs would do a range. or at least allow for an OR statement.

Thanks
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,772
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try:
=SUMPRODUCT(($A$2:$A$8=G2)*($C$2:$C$8=H2)*(SIGN((ISNUMBER(SEARCH(I2,$D$2:$D$8)))+(ISNUMBER(SEARCH(I2,$D$2:$D$8))))))
 

eblake

Active Member
Joined
Aug 18, 2004
Messages
258
Thank you Rory, that worked perfectly. I was looking into something along those lines, just couldn't figure out the details.

Thanks again.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,559
Messages
5,832,488
Members
430,136
Latest member
Asir Jefferson

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
Top