SUMPRODUCT multiple tabs and multiple columns for text

[Jeffers1984]

Hi everyone,

I'm a newbie to this whole Excel thing, but now I have a huge headache trying to figure this puppy out, so any help you can offer would be awesome.

I have an excel sheet with 6 tabs, all containing different data, some text and some numerical.

I need to perfect this formula to cross reference whether an email has was 'Inbound' or 'Pro-active' and if the customer was a new contact or a repeat customer.

I need to search column "D" for the word 'Inbound' on the "Emails" tab and column "E" with the letter "Y" again on the "Emails" tab

So just to clarify:

Column D only contains Text, which can only be 'Inbound' or 'Pro-Active'
Column E only contains text and that is limited to 'Y' and 'N'.

I have tried a few different formula and no luck so far.

=SUMPRODUCT((Emails!E:E="Y")*(Emails!D:D="Inbound")
=SUM(IF(Emails!D:D="Inbound",IF(E:E="Y",1,0),0))
=COUNTIF(Emails!E:E,"Y")AND COUNTIF(Emails!D:D,"Inbound”)
=SUMPRODUCT((Emails!D:D="Inbound"))*( Emails!E:E="Y")))
=SUMPRODUCT((Emails!E:E="Y")*(Emails!D:D="Inbound")

These all come back with either #VALUE! or #NUM!

Any help you can give me would be great!

I hope that all makes sense.

Thanks

Matt.

 

[Jonmo1]

The likely problem is that the Sumproduct and SUM(IF formulas cannot use entire column refs like A:A. Must use FINITE ranges like A1:A1000

Try

=SUMPRODUCT((Emails!E1:E1000="Y")*(Emails!D1:D1000="Inbound"))

 

[Matty]

Welcome to the Board.

What result are you looking for? A count of the records that meet the criteria specified? If so, try:

=SUMPRODUCT(--(Emails!E1:E100="Y"),--(Emails!D1:D100="Inbound"))

Change ranges to suit.

Matty

 

[Jeffers1984]

Thanks to you both, you are wonderful people, jonmo1 you were spot on! you have saved me a long time reporting!

Thank you both!

I'll be back soon for more help, I might even see if I can help someone else too! hahaha!

Matt

 

[Jeffers1984]

Hi everyone,

After coming up with the answer I needed over the weekend, I have put everything into practice for the first time today, all worked fine.

Except... When I copy and pasted the page into an archive file, all the data disappeared, it shows that it is trying to gather the data from the other file, which is not good for me, as I that file is backed up every day!

an example:

This is the original:

=SUMPRODUCT((Emails!E2:E192="Y")*(Emails!D2:D192="Inbound")*(Emails!K2:K192="Vodafone"))

Which turns into:

=SUMPRODUCT(('[Jan 11 Mothersheet.xls]Emails'!E2:E192="Y")*('[Jan 11 Mothersheet.xls]Emails'!D2:D192="Inbound")*('[Jan 11 Mothersheet.xls]Emails'!K2:K192="Vodafone"))

Is there a way I can copy this data from one page to another workbook without this happening.

Thanks for any help you can give me!

Matt

 

[Matty]

Hi Matt,

If you copy a Worksheet to another Workbook, any formulas in the Worksheet will automatically reference the original data. This is the way it is designed.

If you are only interested in archiving the values the formulas return, rather than the formulas themselves, then copy the data and use Paste Special > Values.

Matty