Returning Multiple values based on 3 different criteria

[ncmgamecock]

Hi...

I am a bit confused as how to even begin. I believe a match or index function will work, but i'm just not sure how to make it work.

First, I have a spreadsheet (master data) that pulls from an access database. The data goes back to 2006, and is updated whenever i hit refresh. This data is constantly being updated by over 300 employees. the reason i have this linked to excel, is that it is currently only about 40000 lines (so very manageable).

Second, what i am trying to do is compile various totals from this data set based on several sets of criteria ( i am currently using various sets of pivot tables, but i am now looking for specific criteria to send out in a "tally" fashion). The criteria will be based on Account Director, Target Date, Original Target Date, and Status.

As an example (that i can base other formulas off of), i would need to base the Account Director (in sheet 2, or my final list) to the "master Data" (found in sheet 1, where there is an account director column) set....then find a target date between 3/1/2011 and 3/31/2011...then with a status of "AD Hold"....each set of criteria is in their own column....

I am happy to provide more information.

Thank you in advance for any assistance you can provide!!!!

 

[Akashwani]

Hi,
Welcome to MrExcel.

Take a look here and see if Mike is doing what you require.....

http://www.youtube.com/user/ExcelIsFun#p/search/29/9jmNEqrfrZs

This shows how to extract records based on numerous criteria using Advanced Filters or a formula, the formula is at 7 minutes.

Good luck.

Ak

 

[Akashwani]

Hi,

Sample data......

Excel Workbook
	A	B	C	D	E	F
1	Name	Date1	Animal	Transport	Date2	ID Number
2	tom	01/01/2010	Cow	Bike	01/01/2011	AA10000
3	****	02/01/2010	dog	Car	02/01/2011	AA10001
4	Harry	03/01/2010	Horse	Bus	03/01/2011	AA10002
5	Sally	04/01/2010	Mouse	train	04/01/2011	AA10003
6	Mary	05/01/2010	fish	Tram	05/01/2011	AA10004
7	Tom	06/01/2010	Cow	Plane	01/01/2011	AA10005
8	Jill	07/01/2010	Panda	Tut Tut	07/01/2011	AA10006
9	Jo	08/01/2010	Tiger	Coach	08/01/2011	AA10007
10	Joe	09/01/2010	Lion	Carriage	09/01/2011	AA10008
11	tom	01/01/2010	Cow	Boat	10/01/2011	AA10009
12	Stan	11/01/2010	cow	trike	01/01/2011	AA10010
13	tom	01/01/2010	cow	Bus	01/01/2011	AA10011
14	Dom	13/01/2010	dog	Car	13/01/2011	AA10008
15	Harry	14/01/2010	Horse	Bus	04/01/2011	AA10000
Sheet1

Results using Mike's formula method...

Excel Workbook
	A	B	C	D	E	F
1	Criteria	*	*	*	Count	*
2	Name	Date1	Animal	Date2	3	*
3	Tom	01/01/2010	Cow	01/01/2011	*	*
4	*	*	*	*	*	*
5	Name	Date1	Animal	Transport	Date2	ID Number
6	tom	01/01/2010	Cow	Bike	01/01/2011	AA10000
7	Tom	06/01/2010	Cow	Plane	01/01/2011	AA10005
8	tom	01/01/2010	cow	Bus	01/01/2011	AA10011
Sheet2

The formula in A6 needs to be entered with ctrl shift enter NOT enter.
You can then copy it across and down to suit.

Is this what you would like to do?

Ak

 

[ncmgamecock]

Wow thank you both for your suggestions!!

I was able to successfully use the formula: =SUMPRODUCT((Sheet2!$H$2:$H$80125>=DATE(2011,3,7))*(Sheet2!$H$2:$H$80125<=DATE(2011,4,29))*((Sheet2!$K$2:$K$80125=$A10)*(Sheet2!$AI$2:$AI$80125=$Z$8)))

This formula is able to return the number of "contracts" that meet the criteria specified within the formula. Is there a way i can also find the total dollars associated with these contracts (by adding to this formula)? It would have the same criteria, but utilize columns R and AH to find total contract value.

Thanks in advance for your help!!

 

[Akashwani]

Hi,

With my limited skills and without seeing a sample of your data it is difficult for me to give you the correct solution, you could try this in A6 of Sheet1...

=IF(ROWS(A$6:A6)>$E$2,"",INDEX(Sheet1!R$2:R$80125,SMALL(IF((Sheet2!$H$2:$H$80125>=Date(2011,3,7))*(Sheet2!$H$2:$H$80125<=Date(2011,4,29))*(Sheet2!$K$2:$K$80125=$A$10)*(Sheet2!$AI$2:$AI$80125<=$Z$8),ROW(Sheet2!$R$2:$R$80124)-ROW(Sheet2!$A$2)+1),ROWS(A$6:A6))))

Enter with ctrl shift enter NOT enter.

This formula is based on your Sumproduct result being in E2 of sheet1.

You can try this formula in B6 of sheet1.....

=IF(ROWS(B$6:B6)>$E$2,"",INDEX(Sheet1!AH$2:AH$80125,SMALL(IF((Sheet2!$H$2:$H$80125>=Date(2011,3,7))*(Sheet2!$H$2:$H$80125<=Date(2011,4,29))*(Sheet2!$K$2:$K$80125=$A$10)*(Sheet2!$AI$2:$AI$80125<=$Z$8),ROW(Sheet2!$R$2:$R$80124)-ROW(Sheet2!$A$2)+1),ROWS(B$6:B6))))

Again enter with ctrlt shift enter NOT enter.

Hopefully the above formulas will return all the amounts in R & AH that meet your criteria.

One point that I would like to make about your Sumproduct formula, and I'm no expert so my opinion could be invalid here, I would use a cell reference rather than use......
(Sheet2!$H$2:$H$80125>=Date(2011,3,7))
(Sheet2!$H$2:$H$80125<=Date(2011,4,29))

Example
Sheet2!$H$2:$H$80125>=$A$1)
Sheet2!$H$2:$H$80125<=$A$2)

This will offer you more flexibility, if you want a different date range you will have to change your formula every time, with a cell reference you just type the new criteria into the cell.

I hope that this helps, but it is difficult for me without sample data to work with.

Ak

 

[Akashwani]

Hi,

Using my sample data...

Excel Workbook
	A	B	C	D	E	F
1	Name	Date1	Animal	Transport	Date2	Amount
2	tom	01/01/2010	Cow	Bike	01/01/2011	2,000
3	****	02/01/2010	dog	Car	02/01/2011	200
4	Harry	03/01/2010	Horse	Bus	03/01/2011	2,000
5	Sally	04/01/2010	Mouse	train	04/01/2011	200
6	Mary	05/01/2010	fish	Tram	05/01/2011	200
7	Tom	06/01/2010	Cow	Plane	01/01/2011	20,000
8	Jill	07/01/2010	Panda	Tut Tut	07/01/2011	200
9	Jo	08/01/2010	Tiger	Coach	08/01/2011	200
10	Joe	09/01/2010	Lion	Carriage	09/01/2011	200
11	tom	01/01/2010	Cow	Boat	10/01/2011	20,000
12	Stan	11/01/2010	cow	trike	01/01/2011	2,000
13	tom	01/01/2010	cow	Bus	01/01/2011	2,000
14	Dom	13/01/2010	dog	Car	13/01/2011	200
Sheet1

You can either return the values individually and sum them in F3, or change your sumproduct formula as in F2....

Excel Workbook
	A	B	C	D	E	F
1	Criteria	*	*	*	Count	Total Amount
2	Name	Date1	Animal	Date2	3	24,000
3	Tom	01/01/2010	Cow	01/01/2011	*	24,000
4	*	*	*	*	*	*
5	Name	Date1	Animal	Transport	Date2	Amount
6	tom	01/01/2010	Cow	Bike	01/01/2011	2,000
7	Tom	06/01/2010	Cow	Plane	01/01/2011	20,000
8	tom	01/01/2010	cow	Bus	01/01/2011	2,000
Sheet2

I hope that solves it for you.

Ak