Is it possible to use SUMPRODUCT with labels

  • Thread starter Thread starter F.G.
  • Start date Start date
F

F.G.

Hello,

Is it possible to use sumproduct for labels (named ranges)?

I tried this formula without success:
=SUMPRODUCT((Label1="A")*(Label2="S")*(Label3=0))

I’ve tried to use it jointly with countif:
=Sumproduct((countif(Label1,”A”))*((countif(label2,”S”))*(countif
(Label3,0)))
It doesn’t return the correct results.

I know that if I use the range instead of labels the function works:
=SUMPRODUCT((nra!E2:E659="A")*(nra!F2:F659="S")*(nra!G2:G659=0))

But because of repetitiveness of the function in different ranges over
time I was wondering if there is a way to make it work with labels.

Thank you,
Frank Gashi
 
Named ranges are o.k. if:

1. the dimensions are the same
2. don't use full columns

For example with A1 thru B20:

happy me
happy me
happy me
happy me
happy me
happy me
happy me
happy others
happy others
happy others
sad others
sad others
sad others
sad me
sad me
sad me
sad me
sad me
sad me
sad me

=SUMPRODUCT((A1:A20="sad")*(B1:B20="others")) displays 3
and
=SUMPRODUCT((label1="sad")*(label2="others")) displays 3

after the names have been assigned
 
Named ranges are o.k. if:

1. the dimensions are the same
2. don't use full columns

For example with A1 thru B20:

happy   me
happy   me
happy   me
happy   me
happy   me
happy   me
happy   me
happy   others
happy   others
happy   others
sad     others
sad     others
sad     others
sad     me
sad     me
sad     me
sad     me
sad     me
sad     me
sad     me

=SUMPRODUCT((A1:A20="sad")*(B1:B20="others")) displays 3
and
=SUMPRODUCT((label1="sad")*(label2="others")) displays 3

after the names have been assigned
Click to expand...

Thanks Garry,

I was using full columns.
When limited at same dimension as you suggested it works.

I appreciate your support.

Frank Gashi
 
Back
Top