Results 1 to 11 of 11
Thread: Formula to Count Data (2007)

20080811, 07:11 #1
 Join Date
 Feb 2008
 Posts
 1,514
 Thanks
 137
 Thanked 8 Times in 8 Posts
Formula to Count Data (2007)
I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K
I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored
Your assistance will be most appreciated
I Have Attached both Files. I need the formulas in the File "Summary Data"
I want to calculate
1) How many people have been to the Presentation (Col K) based on the criteria for each item in the summary data Col A
2) How many people have been to the Showhouse (Col L) based on the criteria for each item in the summary data Col A
3) How Many Negative Response there have been (Col O)Marked with a "N" based on the criteria for each item in the summary data Col A
4) How Many Negative Response there have been (Col O)Marked with a "Y" based on the criteria for each item in the summary data Col A
Your assistance will be most appreciated
Regards
Howard
Regards
Howard

20080811, 07:41 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to Count Data (2007)
I would use:
B2:
=COUNTIF([Sample.Data.xls]Combined!$H:$H,A2)
C2:
=SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
D2:
=SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$L$2:$L$7000<>""))
E2:
=SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="N"))
F2:
=SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="Y"))
Then copy B2:F2 down the rows
I presumed that you meant:
4) How Many <font color=red>Positive</font color=red> Response there have been (Col O)Marked with a "Y" based on the criteria for each item in the summary data Col A
Steve

20080811, 07:44 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to Count Data (2007)
<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I think your last 2 formulas should use Column O not M. M is interest. O has the positive or negative feedback...
Steve

20080811, 07:45 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to Count Data (2007)
You're correct, thanks. I will correct my previous reply.

20080811, 07:47 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to Count Data (2007)
Edited by HansV to correct mistake  thanks to Steve for pointing out my mistake!
I would make things much easier if you used the same terms in column A on the summary sheet and in column H on the sample data sheet.
I don't understand requirement 2)  Showhouse is one of the categories and there are no data in column L.
Neither do I understand requirement 4)  wouldn't Y be positive?
You could use the following formulas:
C2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
E2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="N"))
F2: =SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$O$2:$O$7000="Y"))

20080811, 07:59 #6
 Join Date
 Feb 2008
 Posts
 1,514
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Formula to Count Data (2007)
Hi Hans & Steve
Thanks for the replies
Code works perfectly. How can I incorporate a wild card character in the criteria for Eg "*advert*" for eg I want to include Advertisement, Advertorial, Advertising all in one criteria
Your assistance will be most appreciated
Regards
Howard

20080811, 08:15 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to Count Data (2007)
You could use
=SUMPRODUCT(NOT(ISERROR(SEARCH("advert",[Sample.Data.xls]Combined!$H$2:$H$7000)))*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
but the disadvantage is that this can't be filled down.

20080811, 08:42 #8
 Join Date
 Feb 2008
 Posts
 1,514
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Formula to Count Data (2007)
Hi Hans
If I have the word ending in Showhouse in column H, for eg Weekend Shouse, All Day Showhouse how do I change by formula below i.e =$a2 to include these
SUMPRODUCT(([Sample.Data.xls]Combined!$H$2:$H$7000=$A2)*([Sample.Data.xls]Combined!$K$2:$K$7000<>""))
Regards
Howard

20080811, 09:06 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to Count Data (2007)
It would be best to use exactly the same descriptions on both sheets.
Or you could add an extra column to both sheets that contains uniform abbreviations, e.g. "house" for the examples you mention, and use those in the formulas.
Or use the SEARCH function as in my previous reply.

20080811, 09:21 #10
 Join Date
 Feb 2008
 Posts
 1,514
 Thanks
 137
 Thanked 8 Times in 8 Posts
Re: Formula to Count Data (2007)
Hi Hans
Thanks for the help
Regards
Howard

20080811, 10:26 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to Count Data (2007)
To get around the disadvantage tht Hans mentioned with the search, you could add a column (it may be hidden if desired) in each row to list the "search" term. Then this column could be referrred to and the formulas could be copied. This would easier to maintain than hardcoding the search items for each formula.
Steve