Solved

Excel 2010 Sumproduct

Posted on 2014-12-24
6
83 Views
Last Modified: 2015-01-15
I have a total formula in column E that works great.  The formula is [ =IF(B2<>B3,SUMPRODUCT(--($B$2:$B$8000=B2),($C$2:$C$8000)),"")]  My spreadsheet is sorted by the B Number (Column B) and a second level sort on Days (Column D).   I need a formula that can count the ZXZ in column A for the same B number.   I need to know how many B numbers are related to the ZXZ.  For example, the 1st B number is 008589926 and I have 2 ZXZ (Cell A7 and A8) so in cell F8 in should have 4.  I tried several different formulas, but I can’t get it to work.  Please help.  Thank you.
Test-ZXZ.xlsx
0
Comment
Question by:WalterAPO
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40517339
If you want a unique count of the Z and B Number combinations in column F (and it will help if the data remains sorted/grouped as currently), then you can see the sub-totals by inserting this formula in cell F2 and copying down:
=IF((A2&B2)<>(A3&B3),SUMIFS($C$2:$C$8000,$A$2:$A$8000,A2,$B$2:$B$8000,B2),"")

By the way, you could replace your SUMPRODUCT function in column E with this:
=IF(B2<>B3,SUMIF($B$2:$B$8000,B2,$C$2:$C$8000),"")

Additionally, you might look into using PivotTables to summarize your data.  See the attached file for examples of all.

Regards,
-Glenn
EE-Test-ZXZ.xlsx
0
 

Author Comment

by:WalterAPO
ID: 40521845
Glenn,

Thank you for your help, but I need a formula to only add the ZXZ in column A that have the same B number.  I tried to fix your formula, but I can't get it.  Your sumproduct formula works great and the Pivot table is amazing.  Again, thank you.  

Walter
0
 

Author Comment

by:WalterAPO
ID: 40521856
Glenn,

I can't edit my other comment, but I need the Qty added for the ZXZ number that have the same B number.  

Walter
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40521975
Doh!  I apologize; it's right there in the column header!  Here's the corrected formula (in F2, copied down).
=IF(AND(A2="ZXZ",(B2<>B3)),SUMIFS($C$2:$C$8000,$A$2:$A$8000,A2,$B$2:$B$8000,B2),"")

Revised file attached.

-Glenn
EE-Test-ZXZ.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40552553
Hi Walter,
Did you have any additional questions about my revised solution?  Let me know.

-Glenn
0
 

Author Comment

by:WalterAPO
ID: 40552696
Glenn,

Your solution works great.  I was busy and didn't close out my question.  

Thank you,

walter
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

815 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

9 Experts available now in Live!

Get 1:1 Help Now