[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Using Multiple Variables in an Array with Booleans

cardan

12/13/2006 11:33:00 PM

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

Bob Phillips

12/13/2006 11:42:00 PM

0

=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" <carlsondaniel@gmail.com> wrote in message
news:1166052777.140982.8570@f1g2000cwa.googlegroups.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.
>


cardan

12/14/2006 12:13:00 AM

0

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