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

Report •


#1
August 7, 2012 at 16:26:09
✔ Best Answer
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.

MIKE

http://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

Report •

Related Solutions

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

Report •


Ask Question