sumproduct - strange

[UKMAN]

=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

 

[ryguy7272]

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


Regards,
Ryan---

 

[Bob Phillips]

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]

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

 

[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??//

 

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