Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 367
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 229
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 367
Default Add up Values if Find Matches x 2

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

Cheers Carlo


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default Add up Values if Find Matches x 2

Thanks everyone for taking the time to post suggestions

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 454
Default Add up Values if Find Matches x 2

Max, yours worked like a dream, Thanks

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Data from one sheet that matches Brian Shafer Excel Discussion (Misc queries) 1 October 19th 07 06:58 PM
Trying to find Matches and Give a Result cheryl Excel Worksheet Functions 5 July 26th 07 09:06 PM
Using the If or lookup statement to find matches RocketMan Excel Worksheet Functions 3 January 9th 07 06:50 PM
Function to find only exact matches in spreadsheets? Pam1288 Excel Worksheet Functions 3 January 10th 06 07:19 PM
how to find all matches in an array Greg Excel Discussion (Misc queries) 2 August 31st 05 09:04 PM


All times are GMT +1. The time now is 05:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"