maybe vlookup combined with sumifs?

[EvaWOL]

Hi,

Please help!
I have a worksheet where in a row I will have a date (column A), a trailer number (column B), a store number (Column C) and a number of loaded cages displayed (Column D).

In column A the same date might be used multiple times. In column G I have a macro running automatically to fetch unique data from this column A every time something added or removed.

Column B (trailer numbers): each day there are 2 different trailer numbers loaded with different stores. These trailer numbers are recorder multiple times as well as each day each trailer has multiple stores loaded on them with different amount of cages.

On the same sheet I have a summary area, starting with column G. Here I have each date displayed only once, and I need to fetch a sum value for the first and the second trailer separately into columns H and I for that day.

E.g.

Date	Trailer	Store	Amount		Date	Amount on trl1	Amount on trl2
30/05/2018	TRL21	11	4		30/05/2018	need to show 13 for trl21	need to show 14 for trl18
30/05/2018	TRL21	47	5		31/05/2018	need to show 23 for trl08	need to show 15 for trl65
30/05/2018	TRL21	74	4
30/05/2018	TRL18	21	11
30/05/2018	TRL18	18	3
31/05/2018	TRL08	9	9
31/05/2018	TRL08	36	8
31/05/2018	TRL08	24	6
31/05/2018	TRL65	85	14
31/05/2018	TRL65	16	1

<tbody>
</tbody>

I've tried so many different things (like =sumifs which works well for the first date but can't modify it for the rest of the data) but nothing seemed to work. I am really not good with VBA, but seems like this is too complex to have any other solution?

If any ideas, can you please spell it out to me as much as possible? I haven't done super complex things with excel before, and defo nothing google could not help me with, so I feel like a noob with this one...

Thank you!
Eva

 

[jim may]

Re: maybe vlookup combined with sumifs? is that even possible?

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Date	Trailer	Store	Amount			Date	Amount on trl1	Amount on trl2
2	5/30/2018	TRL21	11	4			5/30/2018	13	14
3	5/30/2018	TRL21	47	5			5/31/2018	23	15
4	5/30/2018	TRL21	74	4
5	5/30/2018	TRL18	21	11
6	5/30/2018	TRL18	18	3
7	5/31/2018	TRL08	9	9
8	5/31/2018	TRL08	36	8
9	5/31/2018	TRL08	24	6
10	5/31/2018	TRL65	85	14
11	5/31/2018	TRL65	16	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H2 =SUMPRODUCT(($A$2:$A$100=$G2)*($B$2:$B$100="Trl21")*$D$2:$D$100) I2 =SUMPRODUCT(($A$2:$A$100=$G2)*($B$2:$B$100="Trl18")*$D$2:$D$100) H3 =SUMPRODUCT(($A$2:$A$100=$G3)*($B$2:$B$100="Trl08")*$D$2:$D$100) I3 =SUMPRODUCT(($A$2:$A$100=$G3)*($B$2:$B$100="Trl65")*$D$2:$D$100) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[EvaWOL]

Re: maybe vlookup combined with sumifs? is that even possible?

Thank you Jim,

This would be perfect, but the trailer numbers are changing as well, the only constant I can reference to in a formula here is column G. The next trailer might be TRL10 coming in with a different date and store, and I can not possibly predict what will be typed in

Is it possible to do?

Thanks
Eva

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Trailer	Store	Amount		Date	trl21	trl18	trl08	trl65
2	5/30/2018	TRL21	11	4		5/30/2018	13	14	0	0
3	5/30/2018	TRL21	47	5		5/31/2018	0	0	23	15
4	5/30/2018	TRL21	74	4
5	5/30/2018	TRL18	21	11
6	5/30/2018	TRL18	18	3
7	5/31/2018	TRL08	9	9
8	5/31/2018	TRL08	36	8
9	5/31/2018	TRL08	24	6
10	5/31/2018	TRL65	85	14
11	5/31/2018	TRL65	16	1
Sheet1

In g2 enter, copy across, and down:

=SUMIFS($D$2:$D$11,$A$2:$A$11,$F2,$B$2:$B$11,G$1)

 

[EvaWOL]

Thank you for this too. Unfortunately I do not enter the trailer numbers and they only need statistics to see how many we send on each trailer each day. They want the sheet to be filled out by the clerk from column A to D and want the rest to be automatically updated by formulas, fetching the data from A-D.
Going by my example, trl 1 on the 30th is TRL21 and trl 2 is TRL18, next day the numbers for these change.
I am sorry that I can not explain better

 

[Aladin Akyurek]

Thank you for this too. Unfortunately I do not enter the trailer numbers and they only need statistics to see how many we send on each trailer each day. They want the sheet to be filled out by the clerk from column A to D and want the rest to be automatically updated by formulas, fetching the data from A-D.
Going by my example, trl 1 on the 30th is TRL21 and trl 2 is TRL18, next day the numbers for these change.
I am sorry that I can not explain better

I don't understand what you are saying. In G1:J1 the user needs to enter the trl specs. Are you saying that should be done by formulas not by user?

Have you considered contructiong a pivot table, which does everything you need here automatically?

 

[Marcelo Branco]

If i understand correctly column G is populated by a macro (unique dates). If so, maybe this...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Date​

Trailer​

Store​

Amount​

Date​

TRL21​

TRL18​

TRL08​

TRL65​

2​

05/30/2018​

TRL21​

11​

4​

05/30/2018​

13​

14​

0​

0​

3​

05/30/2018​

TRL21​

47​

5​

05/31/2018​

0​

0​

23​

15​

4​

05/30/2018​

TRL21​

74​

4​

5​

05/30/2018​

TRL18​

21​

11​

6​

05/30/2018​

TRL18​

18​

3​

7​

05/31/2018​

TRL08​

9​

9​

8​

05/31/2018​

TRL08​

36​

8​

9​

05/31/2018​

TRL08​

24​

6​

10​

05/31/2018​

TRL65​

85​

14​

11​

05/31/2018​

TRL65​

16​

1​

12​

<tbody>
</tbody>

Array formula in H1 copied across until you get a blank
=IFERROR(INDEX($B$2:$B$100,SMALL(IF(FREQUENCY(IF($B$2:B$100<>"",MATCH($B$2:$B$100,$B$2:$B$100,0)),ROW($B$2:$B$100)-ROW($B$2)+1),ROW($B$2:$B$100)-ROW($B$2)+1),COLUMNS($H1:H1))),"")
confirmed with Ctrl+Shift+enter, not just Enter

Formula in H2 copied across and down
=IF(H$1="","",SUMIFS($D:$D,$A:$A,$G2,$B:$B,H$1))

M.

 

[EvaWOL]

Hi,

Never thought of a picot table! Some educational reading just appeared on my to do list!
And no, the users will not touch the statistical part of the sheet, they will only use the first 4 columns to fill in the info. They will have a lot more to record than what I filled in as example, some of them is even a noobier beginner at excel than me, so need to make it as simple and straight forward as possible.
Thank you for all the help and suggestions!

 

[Aladin Akyurek]

Hi,

Never thought of a picot table! Some educational reading just appeared on my to do list!
And no, the users will not touch the statistical part of the sheet, they will only use the first 4 columns to fill in the info. They will have a lot more to record than what I filled in as example, some of them is even a noobier beginner at excel than me, so need to make it as simple and straight forward as possible.
Thank you for all the help and suggestions!

The following is something that you would get with a pivot table...

Book1
	A	B	C	D	E	F
14
15	Sum of Amount	Column Labels
16	Row Labels	TRL08	TRL18	TRL21	TRL65	Grand Total
17	5/30/2018		14	13		27
18	5/31/2018	23			15	38
19	Grand Total	23	14	13	15	65
20
Sheet1