Solved

Help with SUMPRODUCT

Posted on 2012-03-30
3
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 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