DVD Talk Forum

DVD Talk Forum (https://forum.dvdtalk.com/)
-   Tech Talk (https://forum.dvdtalk.com/tech-talk-10/)
-   -   Excel Formula assistance (using Arrays?) (https://forum.dvdtalk.com/tech-talk/434962-excel-formula-assistance-using-arrays.html)

68ShelbyGT500KR 08-21-05 09:26 AM

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...


OldDude 08-21-05 09:59 AM

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.

68ShelbyGT500KR 08-21-05 07:39 PM

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"))

All times are GMT -5. The time now is 06:21 PM.

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