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:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Week</td><td style=";">Date</td><td style=";">Contact_Type</td><td style=";">Name</td><td style=";">email</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1/2/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style="text-decoration: underline;color: #0000FF;;">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1/10/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style=";">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1/16/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style=";">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1/25/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style=";">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">12</td><td style="text-align: right;;">3/20/2013</td><td style=";">Person</td><td style=";">jz9999</td><td style=";">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">16</td><td style="text-align: right;;">3/25/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style=";">jz9999@emaildoman.com</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">16</td><td style="text-align: right;;">4/18/2013</td><td style=";">Person</td><td style=";">John Zells</td><td style=";">jz9999@emaildoman.com</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />

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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
36,118
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

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