Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Values if Find Matches x 2
You're welcome.
If something doesn't work just post again. Cheers Carlo |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Values if Find Matches x 2
Thanks everyone for taking the time to post suggestions
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add up Values if Find Matches x 2
Max, yours worked like a dream, Thanks
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Data from one sheet that matches | Excel Discussion (Misc queries) | |||
Trying to find Matches and Give a Result | Excel Worksheet Functions | |||
Using the If or lookup statement to find matches | Excel Worksheet Functions | |||
Function to find only exact matches in spreadsheets? | Excel Worksheet Functions | |||
how to find all matches in an array | Excel Discussion (Misc queries) |