Using Multiple Variables in an Array with Booleans
Alok, Thank you for the response. The formual you presented is very
similar to what I am trying to do. I tried it with SUM and your way
with SUMPRODUCT but I either get 0 or I still get the VALUE error, even
when I convert it to an array. I don't think I need to use OFFSETS as
Bob suggested. As for the "2000" and "Green" I am referencing cells to
link these (I am essentially creating a new table and manually
inputting the variables I want in to automatically put in the month
column.) I tried hard coding but also to no avail. Thanks again for
your response. Dan
Alok wrote:
It is better to use a formula in this case
=sumproduct(--(A1:A1000=2000)*--(C1:C1000="green")*(D1:D1000))
This assumes that numbers like 2000 are in column A, green, red and so on
are in column C and the month numbers are in column D.
Alok
"cardan" wrote:
Hello,
I am having a bit of a problem using Arrays and Booleans that I was
hoping someone could shed some light on. I have a table with mulitple
and repeating descriptors that other formulas that gives me a specific
number in certain months. For example. In row 1 will have a code
number of "2000" which is a code for a certain product. In the next
column on the same row I have another descriptor that I type in such as
"blue" "green", etc...There are other columns with formulas that give
me a specific number on a per month basis. (each month is in its own
column)
I need to sum all the numbers for a specific month that match both
decriptors (code: 2000 and the color green). I have tried Arrays with
Booleans, but always get the number zero.
Any suggestions would be tremendously welcomed! Thanks for your time.
|