Countifs or sumproduct with Wild Cards?

[eblake]

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).

[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

 

[Akashwani]

Hi,

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

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Week	Date	Contact_Type	Name	email	*	Week	Contact Type	Name	Email Contains	*	Count	*
2	1	01/04/2013	Person	John Zells	jz9999@emaildoman.com	*	16	Person	jz9999	jz9999	*	1	*
3	2	02/04/2013	Person	John Zells	jz9999@emaildoman.com	*	*	*	*	*	*	1	*
4	3	03/04/2013	Person	John Zells	jz9999@emaildoman.com	*	*	*	*	*	*	*	*
5	4	04/04/2013	Person	John Zells	jz9999@emaildoman.com	*	*	*	*	*	*	*	*
6	12	05/04/2013	Person	jz9999	jz9999@emaildoman.com	*	*	*	*	*	*	*	*
7	16	06/04/2013	Person	John Zells	jz9999@emaildoman.com	*	*	*	*	*	*	*	*
8	16	07/04/2013	Person	jz9999	jz9999@emaildoman.com	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet3

Second Example....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Week	Date	Contact_Type	Name	email		Week	Contact Type	Name	Email Contains		Count
2	1	01/04/2013	Person	John Zells	jz9999@emaildoman.com		16	Person	jz9999	jz9999		2
3	2	02/04/2013	Person	John Zells	jz9999@emaildoman.com							2
4	3	03/04/2013	Person	John Zells	jz9999@emaildoman.com
5	4	04/04/2013	Person	John Zells	jz9999@emaildoman.com
6	12	05/04/2013	Person	jz9999	jz9999@emaildoman.com
7	16	06/04/2013	Person	jz9999	jz9999@emaildoman.com
8	16	07/04/2013	Person	jz9999	jz9999@emaildoman.com
9
Sheet3

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

I hope that helps.

Ak

 

[eblake]

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]

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]

Thank you Rory, that worked perfectly. I was looking into something along those lines, just couldn't figure out the details.

Thanks again.