Solved

Excel 2010 Sumproduct

Posted on 2014-12-24
6
81 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
Hi Walter,
Did you have any additional questions about my revised solution?  Let me know.

-Glenn
0
 

Author Comment

by:WalterAPO
Comment Utility
Glenn,

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

Thank you,

walter
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

743 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

16 Experts available now in Live!

Get 1:1 Help Now