# Solved Count the number of occurrences in different spreadsheet

August 7, 2012 at 14:12:20
Specs: Windows XP
 Can someone please help me figuring out the Count If formula?I have two tabs in the same workbook in Excel. First tab ("AllRRContributors") lists all reviewers. I need to count the number of review quantity occurrences under "Total Reviews" D column, and if this number is 'Organic' Reviewers (the same I need to do for Email Reviewers and Hybrid Reviewers), then I want to enter this number in the table under "ReviewQ-tyBreakdown" tab. The AllRRContributors table under 1st tab in the spreadsheet looks like this:`A D K``ID Total Reviews Campaign Code ``Reviewer #1 10 Organic``Reviewer #2 10 Organic``Reviewer #3 52 Email``Reviewer #4 52 Email ``Reviewer #5 33 Organic ``Reviewer #6 52 Email ``Reviewer #7 15 Email ``Reviewer #8 2 Hybrid ``Reviewer #9 2 Organicl ``Reviewer #10 40 Email ``Reviewer #11 52 Email ``Reviewer #12 63 Organic ``Reviewer #13 152 Organic ``Reviewer #14 81 Email ``Reviewer #15 52 Email `This table has 500,000 rows.The ReviewQ-tyBreakdown table under 2nd tab looks like this:`A B C D``Review Q-ty OrganicReviewers EmailReviewers HybridReviewers ``1 ``2 ``3 ``4 ``5 ``6 ``7 ``8 ``9 ``10 2``11 ``12 ``13 ``14 ``15 ` I need this formula(s) count the number of people who wrote 1 review, 2 reviews, 3 reviews,4 reviews and so on, in first tab and enter this number in appropriate column in the second tab. For example, . there are two reviewers (Reviewer # 1 and Reviewer #2) who wrote 10 reviews each. So number 2 should be entered in the ReviewQ-tyBreakdown table under 2nd tab under "OrganicReviewers" column in respective "Review Q-ty" row, which is 10 reviews.I tried to create the formula below but it didn't work:=COUNTIF(AllRRContributors!\$D2:D510579,"=A3","Organic"), where B2 represents the Review Q-ty of 1 review in OrganicReviewers column.Thank you in advance!

See More: Count the number of occurrences in different spreadsheet

#1
August 7, 2012 at 16:26:09
 Try these and see how they work:For column B enter the formula:=SUMPRODUCT(--(AllRRContributors!\$D\$2:\$D\$510579=\$A2),--(AllRRContributors!\$K\$2:\$K\$510579="Organic"))For column C enter the formula:=SUMPRODUCT(--(AllRRContributors!\$D\$2:\$D\$510579=\$A2),--(AllRRContributors!\$K\$2:\$K\$510579="Email"))For column D enter the formula:=SUMPRODUCT(--(AllRRContributors!\$D\$2:\$D\$510579=\$A2),--(AllRRContributors!\$K\$2:\$K\$510579="Hybrid"))Then drag them down as many rows as needed.MIKEhttp://www.skeptic.com/

Report •

#2
August 8, 2012 at 07:26:23
 Hi mmcconaghy,The formula works magically! I never used SUMPRODUCT function....Can you please explain how this formula works, and what the two dashes (--)in the formula represent? Thank you again!

Report •

#3
August 8, 2012 at 07:35:27
 Here's a good explaination:http://chandoo.org/wp/2009/11/10/ex...MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
August 8, 2012 at 08:57:06
 Thank you!!!

Report •