Formula to find three different criteria and then sum any matches

**Weasel** · 11-01-2010, 06:10 PM

Hello,

I need a formula that will find a sum any matches based off of four different criteria. The criteria are as follows:

1. Going down column A, starting in Row 10, are a bunch of numbers
2. Going down column B, starting in Row 10, are cells containing descriptions made up of multiple words. Inside the description is a specific word I need to match
3. Going across row 8 are locations
4. Going across row 9 are headers for sales and on hand. The location in row 8 is listed above the sales column and nothing is listed above the on hand column.

I need a formula that, if it finds a match for a specific number in row A, a specific word in column B, and the specific location in row 8, will then display the sum of all sales. Same goes for showing the sum of all on hand. The totals will then be displayed on a reporting spreadsheet which will contain the specific criteria I'm trying to total.

Any help would be greatly appreciated. Thanks

**teylyn** · 11-01-2010, 06:35 PM

Weasel, please post a workbook with a few rows of data and manually mock up the expected results.

**Weasel** · 11-01-2010, 10:06 PM

ok, I attached a sample with a tab for the report sheet and the data sheet. As an example, I typed in the word Jack in cell A1. If I changed the word in cell A1 it would look for the new criteria. I would love for the formula to be setup to ignore that specific criteria if cell A1 was left blank and only be concerned with matching the location and size

**JBeaucaire** · 11-01-2010, 11:06 PM

Your Location names in the report didn't match exactly the Location names in the data sheet row 8. They match now.

There are two formulas, one in B3 and one in C3. Those two are copied as a pair down and across the rest of the table.

**teylyn** · 11-01-2010, 11:34 PM

JB, the formulas in B3 and C3 are identical. Did you mean it that way? Also, the formulas don't seem to react on the content of A1.

Here's a different approach with Sumproduct. If you enter a word, e.g. Jack or OLD in A1 (case does not matter), only the pertinent data will be summed. If A1 is blank, all will be summed.

For this to work, I got rid of the merged cells and entered data labels in each column. With matching data, of course.

Please Login or Register  to view this content.

see attached

cheers,

**JBeaucaire** · 11-02-2010, 12:03 AM

I corrected the wb attached to my post above, no idea where the second formula lost its "tweak", though it is pretty tiny.

Originally Posted by Weasel

I would love for the formula to be setup to ignore that specific criteria if cell A1 was left blank and only be concerned with matching the location and size

This works off of the sizes and and the locations.

**Weasel** · 11-02-2010, 01:14 AM

Teylen,

I see in your solution you ended up adding the location name to the on hand column. Unfortunately that isn't an option for me. Is it possible to modify to fix this?

JBeaucaire,

I downloaded your sample but it doesn't seem to also be searching for the matching name I list in cell A1. As far as I can tell it's only matching the size and location. Is it possibly to modify to also include the name search?

I thank you both for your help with this

**DonkeyOte** · 11-02-2010, 03:07 AM

Weasel, is "Data" actually a PivotTable ?

If so I would suggest you use GETPIVOTDATA to retrieve the value
Wrap the call in a suitable handler to account for errors
XL2007+ use IFERROR, prior to that I would suggest LOOKUP(9.99E+307,CHOOSE({1,2},0,GETPIVOTDATA(...)))

**Weasel** · 11-02-2010, 10:31 AM

Originally Posted by DonkeyOte

Weasel, is "Data" actually a PivotTable ?[/I]

No, it's not a PivotTable. It's just a data dump

**Weasel** · 11-02-2010, 01:49 PM

I forgot to also mention that the Location names in row 8 can move around each time I run the data dump. So for example if I run it this week location 1 could be the first location listed but if I run it next week it could be the third location listed. The formula has to be dynamic enough to be able to search all of row 8 for the location name

**Weasel** · 11-03-2010, 12:44 PM

Can anyone else help me with this?

Formula to find three different criteria and then sum any matches

LinkBack

Thread Tools

Rate This Thread

Display

Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Re: Formula to find three different criteria and then sum any matches

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions