Matching data

[kristy_6278]

How do i work out which formular to use for example.

I have 4000 plublication entries Column B and I need to fine Globe and Mail
and Match it to Column D with the regions. I.e hpw many entries of Globe and
Mail appear and match up to the National Region?

 

[Shane Devenshire]

Hi,

First, not enought info - how do you determine they are in the National
Region?

Basically the formula would be
=SUMPRODUCT(--(B1:B4000="Globe")+(B1:B4000="Mail"),--(D14000="National
Region"))

 

[kristy_6278]

Thanks so much Shane! Sorry for the lack of detail.

I have 3 months (different worksheets) July, August and September.

Then the 4th workbook I am putting the results.

All layouts are the same for each month workbook.
I have one column called Service Lines, (Column J) and there are 4 service
lines distributed through out the 400 lines. Then I have another column with
regions (Column K) Again there are 3 criteria, National/Central, Easter or
Western. I want to see which service lines match up to which region. I.e
Toys, how many times does toys match up to National/Central.

Does that make more sense?

 

[kristy_6278]

=SUMPRODUCT(--(July!J:J="AABS")+(July!K:K="National/Central"))

I tried this and got #NUM??

 

[David Biddulph]

If you are using a version of Excel prior to 2007, trying to use a full
column (such as J:J) in SUMPRODUCT will give #NUM!
You need to specify start and end points in the column, e.g.
=SUMPRODUCT(--(July!J1:J100="AABS")+(July!K1:K100="National/Central"))

But what are you trying to do with the formula? What is the double unary
minus trying to do on the J term, as you're already doing an arithmetic
operation on it by adding the K term?

If you want the J and K terms to be combined with an AND, you may want
=SUMPRODUCT(--(July!J1:J100="AABS"),--(July!K1:K100="National/Central")) or
=SUMPRODUCT((July!J1:J100="AABS")*(July!K1:K100="National/Central"))

Your current formula will count 1 for an exclusive OR of the J and K terms,
but would count 2 if J and K terms were true on the same row. Are you sure
that's what you want?
If you want to combine the J and K terms with an OR, then perhaps
=SUMPRODUCT(SIGN((July!J1:J100="AABS")+(July!K1:K100="National/Central"))) ?

 

[kristy_6278]

=SUMPRODUCT(--(July!J2:J470 = "AABS"),--(July!K2:K470 = "National/Central"))

THIS WORKED LIKE A DREAM!! THANK YOU SO MUCH!! I can now start to calculate
the results! Whoooooooooooo you have made my day!