Solved

Help with SUMPRODUCT

Posted on 2012-03-30
3
248 Views
Last Modified: 2012-03-30
I need help creating a formula to count values in a table. This is what I have:

Pages   Pages Sent
3          3
4          10
5           5
2           12
6            6

I need to count the rows that have the same value in both columns and another count where the second value is bigger than the first one:
Same Amount of Pages: 3
Sent Duplicate Pages: 2

Thanks....
0
Comment
Question by:horalia
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 37788381
You can use array functions to calculate these values.  Press [Ctrl]+[Shift]+[Enter] to complete the entry.

Assuming your example values are in columns A & B, then

For Same Amount:
{=SUM(IF((A2:A6)=(B2:B6),1,0))}

For Duplicate Pages:
{=SUM(IF((A2:A6)<(B2:B6),1,0))}

-Glenn
0
 

Author Closing Comment

by:horalia
ID: 37788416
Gotta work more with arrays... Thanks!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37788439
You could also do both with a non-array SUMPRODUCT as you suggest, e.g. for the first

=SUMPRODUCT((A2:A6=B2:B6)+0)

regards, barry
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now