Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Excel Formula assistance (using Arrays?)

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel Formula assistance (using Arrays?)

Old 08-21-05, 10:26 AM
DVD Talk Gold Edition
Thread Starter
Join Date: Jun 2004
Location: Houston, Tx.
Posts: 2,713
Likes: 0
Received 0 Likes on 0 Posts
Excel Formula assistance (using Arrays?)

Need A little Excel Formula assistance that is pulling my hair out.
Below is a sample shot of "Sheet 1" in a workbook.
I have tries various functions and at wits end. I need Professional help in this problem.
a)For example, I am working with Column "E" (Colors) and Column "F" (Model).

b)I want the formula to look for all the "Legend Lime" colors in Column "E" and then look for all of the "GT" models in Column "F"

c) I need to see a count (total amount) of Legend Lime GT's as the result in the "Sheet 2", in cell B2

d) Then if that is accomplished, I will want to have total count of all Legend Lime V6's in Cell B2 of Sheet 2.

Hope the image below helps.
Thanks for any suggestions...

Old 08-21-05, 10:59 AM
DVD Talk Hero
Join Date: Jul 2001
Location: MI
Posts: 25,077
Likes: 0
Received 7 Likes on 5 Posts
I don't know how to do it exactly as you are proposing.

I would introduce an auxilliary column, hiding it if necessary. For each row, I would use AND statements to connect the multiple requirements, such that each row would be a 1 if met, zero if not met. Then I would use COUNTIF. I would also add two (or more) "query cells" where I could type in color and model I wanted to search for, and those values would be used (as fixed cell addresses) for the comparator in each row of data.

Not real elegant, but I'm pretty sure it would work. Maybe elegance isn't required.

My only other suggestion would be to use a "real database" where this is routine. Access is the obvious choice as part of Office suite, but there are many others.
Old 08-21-05, 08:39 PM
DVD Talk Gold Edition
Thread Starter
Join Date: Jun 2004
Location: Houston, Tx.
Posts: 2,713
Likes: 0
Received 0 Likes on 0 Posts
I Finally, have the sheet working now!
I had to use the SUMPRODUCT function

=SUMPRODUCT((( Sheet1!$E$1:$E$10) = "Legend Lime")*((Sheet1!$F$1:$F$10) = "GT"))

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information -

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.