Using Multiple Variables in an Array with Booleans
 

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.

Using Multiple Variables in an Array with Booleans
 

=SUMPRODUCT((A1:A20=2000)*(B1:B20="green")*(OFFSET (C1,0,month-1,20)))

The 20 in the OFFSET refers to the 20 rows, adjust with the ranges.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"cardan" wrote in message
s.com...
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.

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.