=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman

Does this work for you?
=SUMPRODUCT(--(A5:A504="Permanent"),AY5:AY504)


Regards,
Ryan---

--
RyGuy


"UKMAN" wrote:

=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman

Works fine for me but why not just use

=SUMIF(A:A,"Permanent",AY:AY)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"UKMAN" wrote in message
...
=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be
blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman

hi,

same result as with mine? I have to list the "Permanet" in the first row for
it to work.

by the way in my example the rows start at A5 not A1 as I menetioned so
formula correct. :)

its so <:? strange...

"ryguy7272" wrote:

Does this work for you?
=SUMPRODUCT(--(A5:A504="Permanent"),AY5:AY504)


Regards,
Ryan---

--
RyGuy


"UKMAN" wrote:

=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman

hi,

same result as with mine? I have to list the "Permanet" in the first row for
it to work.

In my example I said A1 as first row in fact it is A5 so formula is correct.

any other ideas??//

"Bob Phillips" wrote:

Works fine for me but why not just use

=SUMIF(A:A,"Permanent",AY:AY)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"UKMAN" wrote in message
...
=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be
blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman

many thanks for your help but I think I have sorted it.. :)

The value I was totaling was on the top row of the series of 4 and as the
variable i.e. "Permanent" was on the second row of the series it didn't
work... hope that makes sence.


"UKMAN" wrote:

=SUMPRODUCT((A5:A504="Permanent")*AY5:AY504)

Colmun A cells can either be blank or hold data in sets of 4 rows i.e.

a1 will be "=con1"
a2 will be either "Permanent", Associate or Contractor
a3 & a4 will empty

this then repeated for next 4 rows etc changing the "=con?" for each set
upto "=con100"

Column AY cells will hold the value of a calculation i.e. "=ad5" or be blank.

In essence what I am doing is totaling the values (ay cells) for each
category of "Permanent", Associate or Contractor

Strange point is that if I have the "Permanent", Associate and Contractor"
listed in the cell before the "=con?" i.e. swap a1 and a2 around cell the
above formula works otherwise no calculation is done?

As ever any questions please ask but many thanks in advance.

Ukman