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

Excel Formula assistance (using Arrays?)

Old 08-21-05, 09:26 AM
  #1  
DVD Talk Gold Edition
Thread Starter
 
Join Date: Jun 2004
Location: Houston, Tx.
Posts: 2,713
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...

68ShelbyGT500KR is offline  
Old 08-21-05, 09:59 AM
  #2  
DVD Talk Hero
 
Join Date: Jul 2001
Location: MI
Posts: 25,045
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.
OldDude is offline  
Old 08-21-05, 07:39 PM
  #3  
DVD Talk Gold Edition
Thread Starter
 
Join Date: Jun 2004
Location: Houston, Tx.
Posts: 2,713
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"))
68ShelbyGT500KR is offline  

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

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