Using Multiple Variables in an Array with Booleans

[cardan]

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.

 

[Bob Phillips]

=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]

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