Creating Array formulas with multiple criteria

I need to use either sumif or match or whatever using multiple criteria. The
data is on 2 different worksheets.
Each sheet has 4 columns of data: Name, Company, Date, Charge
Because of the sheet layout, I'm not using lables
I need to search the second sheet and add the charges of every row that
matches the Name, Company and Date for each line on the first sheet.
Example: First Sheet
| Name | Company | Date | Charge |
| Joe | Shop | Mar 3 | $100.00 |
| Bill | Office | Mar 3 | $50.00 |
| Tom | Shop | Dec 6 | $100.00 |
Second Sheet
| Joe | Shop | Mar 3 | $25.00 |
| Bill | Office | Dec 6 | $50.00 |
| Joe | Shop | Mar 3 | $75.00 |
| Tom | Shop | Dec 6 | $100.00 |
For the first row of the first sheet, the search will add the charges from
rows 1 & 3 on the second sheet because the first three values match.

Any ideas out there?

Hi

D2=SUMPRODUCT(--('First Sheet'!$A$2:$A$1000=A2),--('First
Sheet'!$B$2:$B$1000=B2),--('First Sheet'!$C$2:$C$1000=C2),'First
Sheet'!$D$2:$D$1000)
Adjust ranges for your layout, and copy the formula down. You can enchance
the formula above, defining all ranges referred to as dynamic named ranges,
so the formula will adjust automatically whenever rows are added/deleted on
sheet 'First Sheet'.

Arvi Laanemets



"Space Elf" <SpaceElf@discussions.microsoft.com> wrote in message
news:6F349EA6-E18A-4D14-9ACB-08E2522632F9@microsoft.com...
> I need to use either sumif or match or whatever using multiple criteria.
The
> data is on 2 different worksheets.
> Each sheet has 4 columns of data: Name, Company, Date, Charge
> Because of the sheet layout, I'm not using lables
> I need to search the second sheet and add the charges of every row that
> matches the Name, Company and Date for each line on the first sheet.
> Example: First Sheet
> | Name | Company | Date | Charge |
> | Joe | Shop | Mar 3 | $100.00 |
> | Bill | Office | Mar 3 | $50.00 |
> | Tom | Shop | Dec 6 | $100.00 |
> Second Sheet
> | Joe | Shop | Mar 3 | $25.00 |
> | Bill | Office | Dec 6 | $50.00 |
> | Joe | Shop | Mar 3 | $75.00 |
> | Tom | Shop | Dec 6 | $100.00 |
> For the first row of the first sheet, the search will add the charges from
> rows 1 & 3 on the second sheet because the first three values match.
>
> Any ideas out there?
>

"Space Elf" <SpaceElf@discussions.microsoft.com> wrote in message
news:6F349EA6-E18A-4D14-9ACB-08E2522632F9@microsoft.com...
>I need to use either sumif or match or whatever using multiple criteria.
>The
> data is on 2 different worksheets.
> Each sheet has 4 columns of data: Name, Company, Date, Charge
> Because of the sheet layout, I'm not using lables
> I need to search the second sheet and add the charges of every row that
> matches the Name, Company and Date for each line on the first sheet.
> Example: First Sheet
> | Name | Company | Date | Charge |
> | Joe | Shop | Mar 3 | $100.00 |
> | Bill | Office | Mar 3 | $50.00 |
> | Tom | Shop | Dec 6 | $100.00 |
> Second Sheet
> | Joe | Shop | Mar 3 | $25.00 |
> | Bill | Office | Dec 6 | $50.00 |
> | Joe | Shop | Mar 3 | $75.00 |
> | Tom | Shop | Dec 6 | $100.00 |
> For the first row of the first sheet, the search will add the charges from
> rows 1 & 3 on the second sheet because the first three values match.
>
> Any ideas out there?

Use SUMPRODUCT to test multiple criteria (SUMIF used simply can use only one
criterion).

See this:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Ian