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