[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How sumproduct & counta with a twist?

Posted on 2014-04-26
6
Medium Priority
?
407 Views
Last Modified: 2014-04-27
I need a macro that would do the following:
Look in column A and make list of all "Products" listing just the first "Serial" number.
Then sum all three "TYPE"...MX, SX and HX.

I have attached a file to better explain.
Jimi
sumproduct---counta-with-a-twist.xlsx
0
Comment
Question by:Jimi Sherman
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:als315
ID: 40025375
You can do it with pivot table (just hide some columns and First Serial Number is minimum)
sumproduct---counta-with-a-twist.xlsx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40025384
You can use COUNTIFS for those results. The formula in G2 would be:

=COUNTIFS(A:A,$E2,C:C,G$1)

Flyster
sumproduct---counta-with-a-twist.xlsx
0
 

Author Comment

by:Jimi Sherman
ID: 40025385
A pivot table will not work for what I doing. The worksheet will change daily and this is only one part of a bigger process I am putting together.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:Jimi Sherman
ID: 40025388
Hi Flyster,
But what about this part...making list of all "Products" listing just the first "Serial" number?
0
 
LVL 22

Accepted Solution

by:
Flyster earned 2000 total points
ID: 40025415
Took a little time for this one. For the List, you can use this formula:

=INDEX($B$2:$B$38, MATCH(0, COUNTIF($F$1:F1, $B$2:$B$38), 0))

To list the first serial number, I added a column which counts the number of occurrences of each product. You can hide it.  Then for serial number you use this:

=SUMPRODUCT((B2:B38=$F2)*(A2:A38=1)*(C2:C38))
sumproduct---counta-with-a-twist.xlsx
0
 

Author Closing Comment

by:Jimi Sherman
ID: 40026248
That will work. Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

590 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