SUMPRODUCT for a two-dimensional lookup with text

[tinderbox22]

Hello,

I'm looking to do a SUMPRODUCT to add up numbers with multiple criteria. The issue I'm running into is that a #VALUE error occurs due to text being included within the data. I can not remove this text, as it's part of the original data set (e.g. Date ranges as headings within the data). So, how would I go about allowing the formula to ignore the #VALUE errors it's picking up within the array?

Simplified sample might look like this:

B1:H1: Weekly date range (1/7, 1/14, 1/21, etc.)
A2:A20: City Name
B2:B20: Tactic Name
C2:H20: Data (some numbers, some text)

=SUMPRODUCT((Data)*(Tactic="Tactic1")*(City="City1"))
My SUMPRODUCT needs to match the columns with a date and the rows with a city name and tactic name. The text is causing a #VALUE error.

Any help would be appreciated.
Thank you.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[63falcondude]

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[tinderbox22]

File attached.
I made a very simplified set of fake data for you to evaluate. There are two tabs - "without text" and "with text."
You said you didn't want to see the errors, but this is the only way to showcase what I'm trying to do.
The "with text" tab shows the errors while the other one shows the correct results.

Thank you!
Ryan

[Pete_UK]

Change the formula in C21 to this:

=SUMPRODUCT($D$3:$O$18,($D$2:$O$2=$B21)*($B$3:$B$18="Video")*($A$3:$A$18=C$20))

(changes made to the red term). You can then copy this across and down, as required.

Hope this helps.

Pete

[tinderbox22]

That did it!
Thank you. I had the asterisk instead of the comma.

Appreciate the quick responses and the help!

[Pete_UK]

You're welcome.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete