[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# How sumproduct & counta with a twist?

Posted on 2014-04-26
Medium Priority
407 Views
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
Question by:Jimi Sherman
• 3
• 2

LVL 40

Expert Comment

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

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

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

Author Comment

ID: 40025388
Hi Flyster,
0

LVL 22

Accepted Solution

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

ID: 40026248
That will work. Thanks
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll