Add up Values if Find Matches x 2
 

I'm looking for a formula that will do this

Add up all values (in Col A) that have a match (in Col C) to a word
that I hold in SheetB A1, that in addition have a match (in Col B)
that falls between two dates I hold in SheetB A1 & B1

What I'm trying to do is total all Sales Invoices for certain named
customers for each of a 4 week period. My data is in one sheet,
database format, my parameters in SheetB.

Don't wish to use a Pivot Table

Thanks

Add up Values if Find Matches x 2
 

Hi Sean

this formula should do what you want, but only if the Dates are
sorted:

=SUMIF(INDIRECT("C" & MATCH(SheetB!A2,B:B,0) &":C"&
MATCH(SheetB!B2,B:B)),SheetB!A1,INDIRECT("A" &
MATCH(SheetB!A2,B:B,0) & ":A"& MATCH(SheetB!B2,B:B)))

Although the cells you posted for your dates in Sheet B are the same
as your word. so I changed them to
Word: SheetB!A1
Date1: SheetB!A2 (First Day for lookup)
Date2: SheetB!B2 (Last Day for lookup)

I don't know how you could solve it if the dates are not sorted.

Hth

Carlo

Add up Values if Find Matches x 2
 

Thanks Carlo, I'll have a go at that. On the Date cell, I was trying
to simplify, they are not actual cells I use

Add up Values if Find Matches x 2
 

Use this formula and change the ranges appropriately

=SUMPRODUCT(--(Sheet1!$C$2:$C$10=Sheet2!A1)*--(Sheet1!$B$2:$B$10=Sheet2!A5)*--(Sheet1!$B$2:$B$10<=Sheet2!B5)*Sheet1!$A$2:$A$10)

sheet2-A5 and B5 are the from and to dates.

With regards
Sridhar
"Sean" wrote:

I'm looking for a formula that will do this

Add up all values (in Col A) that have a match (in Col C) to a word
that I hold in SheetB A1, that in addition have a match (in Col B)
that falls between two dates I hold in SheetB A1 & B1

What I'm trying to do is total all Sales Invoices for certain named
customers for each of a 4 week period. My data is in one sheet,
database format, my parameters in SheetB.

Don't wish to use a Pivot Table

Thanks

Add up Values if Find Matches x 2
 

You're welcome.
If something doesn't work just post again.

Cheers Carlo

Add up Values if Find Matches x 2
 

Another thought ..

Source data assumed in sheet: x, from row2 down

In SheetB,
In A1: <word
In B1: start date
In C1: end date

Put in D1:
=SUMPRODUCT((x!C$2:C$10=A1)*(x!B$2:B$10=B1)*(x!B$ 2:B$10<=C1),x!A$2:A$10)

If the word input in A1 could be part of a text string in x's col C
(instead of an exact match), you could use this instead in D1:
=SUMPRODUCT((ISNUMBER(SEARCH(A1,x!C$2:C$10)))*(x!B $2:B$10=B1)*(x!B$2:B$10<=C1),x!A$2:A$10)

Dates need not be sorted in x's col B (these are assumed real dates)

Adapt the ranges to suit the actual extent of the source data in x
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sean" wrote:
I'm looking for a formula that will do this

Add up all values (in Col A) that have a match (in Col C) to a word
that I hold in SheetB A1, that in addition have a match (in Col B)
that falls between two dates I hold in SheetB A1 & B1

What I'm trying to do is total all Sales Invoices for certain named
customers for each of a 4 week period. My data is in one sheet,
database format, my parameters in SheetB.

Don't wish to use a Pivot Table

Thanks

Add up Values if Find Matches x 2
 

Thanks everyone for taking the time to post suggestions

Add up Values if Find Matches x 2
 

"Sean" wrote:
Thanks everyone for taking the time to post suggestions

Appreciate your thanks.
But please feedback whether it worked for you. cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Add up Values if Find Matches x 2
 

Max, yours worked like a dream, Thanks

Add up Values if Find Matches x 2
 

"Sean" wrote
Max, yours worked like a dream, Thanks

welcome, glad to hear that, Sean.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---