# Excel 2010 Sumproduct

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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel VBA DeveloperCommented:
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 Commented:
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 Commented:
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
Excel VBA DeveloperCommented:
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
Excel VBA DeveloperCommented:
Hi Walter,
Did you have any additional questions about my revised solution?  Let me know.

-Glenn
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Glenn,

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

Thank you,

walter
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.