What is the best way to look up 2 text criteria and return a number answer

[drumfin]

Hi

I have to use a daily report to pull in the figures onto a overall spreadsheet.



One of the problems are the section heading will vary I might not always have the same headings e.g. i might not always have anything under golf balls shipped

	19.04.16
Golf Balls (Total orders)	10
Order Assigned	4
Sent to Picking	3
Invoice Raised	2
Shipped	1
Grand Total	10

<colgroup><col><col></colgroup><tbody>
</tbody>

How do I look up the name Fist heading in Column A - Golf Balls, then look up a second condition - "Shipped" under this sub heading, then return the actual figure when I have have similar subheading for other products

DAILY FIGURES SHEET

Orders
	19.04.16
Golf Balls (Total orders)	10
Order Assigned	4
Sent to Picking	3
Invoice Raised	2
Shipped	1
Grand Total	10

<colgroup><col><col></colgroup><tbody>
</tbody>

RUNNING WEEKLY TOTALS SHEET

Orders
	Total	19.04.16	20.04.16	21.04.16
Golf Balls
Order Assigned
Sent to Picking
Invoice Raised
Shipped
Buggies
Order Assigned
Sent to Picking
Invoice Raised
Shipped
Grand Total

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

I would be glad of any help on this

 

[oldbrewer]

19.04.16

Golf Balls (Total orders)

100

1

I am interested in

Golf Balls

10

Order Assigned

40

Sent to Picking

30

Invoice Raised

20

how many were shipped

10

Shipped

10

Grand Total

10

Golf Bags (Total orders)

10

Order Assigned

4

formula giving 10

Sent to Picking

3

=OFFSET($C$1,MATCH(1,$C$2:$C$13,0)+4,-1)

Invoice Raised

2

Shipped

1

Grand Total

10

this helper column

puts a 1 if

cells in A column

contain

"total orders"

and the left

part of the text

equals cell K2

formula giving 1

=IF(ISERROR(SEARCH("Total orders",A2)),"",IF(LEFT(A2,10)=K2,1,""))

the number shipped

ia offset from the 1

<colgroup><col><col span="8"><col><col span="5"></colgroup><tbody>
</tbody>

 

[drumfin]

19.04.16

Golf Balls (Total orders)

100

1

I am interested in

Golf Balls

10

Order Assigned

40

Sent to Picking

30

Invoice Raised

20

how many were shipped

10

Shipped

10

Grand Total

10

Golf Bags (Total orders)

10

Order Assigned

4

formula giving 10

Sent to Picking

3

=OFFSET($C$1,MATCH(1,$C$2:$C$13,0)+4,-1)

Invoice Raised

2

Shipped

1

Grand Total

10

this helper column

puts a 1 if

cells in A column

contain

"total orders"

and the left

part of the text

equals cell K2

formula giving 1

=IF(ISERROR(SEARCH("Total orders",A2)),"",IF(LEFT(A2,10)=K2,1,""))

the number shipped

ia offset from the 1

<tbody>
</tbody>

Hi thanks for the reply but I'm not sure what I am supposed to do here

 

[oldbrewer]

For simplicity I used 2 orders. I used a helper column which detects what you are interested in - ie golf balls which was entered in the cell after "I am interested in". The 10 is calculated from the formula I posted. You have to apply this method - if you choose to - to your own data.

Will you only want one item at a time or a list of selected items ?

 

[drumfin]

For simplicity I used 2 orders. I used a helper column which detects what you are interested in - ie golf balls which was entered in the cell after "I am interested in". The 10 is calculated from the formula I posted. You have to apply this method - if you choose to - to your own data.

Will you only want one item at a time or a list of selected items ?

Hi

I will be looking for two items of a time as i will have a need to look up the Golf ball categories and then return a figure for the subcategory golf balls, orders assigned to reuturn a figure of 40 if that makes sense.

Thanks for you help

 

[oldbrewer]

so use offset match to find Golf Balls and add 1 to drop down to order assigned and go right one to get the number next to order assigned

 

[drumfin]

so use offset match to find Golf Balls and add 1 to drop down to order assigned and go right one to get the number next to order assigned

Thanks I will try that