VLOOKUP, MATCH or INDEX to obtain last inmediatly value on changing column In XL07

[Sirmarte]

Hi,


I have one worksheet. I am needing to match up column G cells (TELCEL/MULTI) with value on E3 and with value E4 and give consecutive numbers on column F depending if the matched column G with value on E3/E4 is on same date of column L and if column Q (Tipo de movimiento) appers "Ventas"; I have plenty blankcells and other values on column G.
I trying catch up with excel..
Thanks 4 your help


[TABLE="width: 1058"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]column E

[/TD]
[TD]# DE EVENTO[/TD]
[TD]COMPAÑÍA[/TD]
[TD]INICIO[/TD]
[TD]MES[/TD]
[TD]Clave del producto[/TD]
[TD]Descripción[/TD]
[TD]Fecha del movimiento[/TD]
[TD]Clave de la tienda[/TD]
[TD]Clave de la caja[/TD]
[TD]Clave del usuario[/TD]
[TD]Clave del vendedor[/TD]
[TD]Tipo de movimiento[/TD]
[/TR]
[TR]
[TD="align: right"]row3[/TD]
[TD]TELCEL[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABR[/TD]
[TD]may[/TD]
[TD]ABRIR[/TD]
[TD]abrir cajon dinero[/TD]
[TD]01/05/2014[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Ventas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MULTI[/TD]
[TD][/TD]
[TD][/TD]
[TD]785[/TD]
[TD]may[/TD]
[TD]785120754179[/TD]
[TD]Mavilan, Lanzoprasol 30 mg C/14 Caps[/TD]
[TD]01/05/2014[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Ventas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]750[/TD]
[TD]may[/TD]
[TD]7503001007168[/TD]
[TD]Mexapin, Ampicilina de 500mg c/20 Caps[/TD]
[TD]01/05/2014[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Ventas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]750[/TD]
[TD]may[/TD]
[TD]7501537102418[/TD]
[TD]Ulcevit, Ranitidina 300 mg c/10 grageas[/TD]
[TD]01/05/2014[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Ventas[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]I have to put 1 to first and then sum one if condition is true[/TD]
[TD]1[/TD]
[TD]TELCEL[/TD]
[TD]TAT[/TD]
[TD]may[/TD]
[TD]TAT31[/TD]
[TD]Telcel, Telcel tiempo aire $31[/TD]
[TD]01/05/2014[/TD]
[TD]01[/TD]
[TD]01[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Ventas[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

Hello, and welcome to the board.

I'm not quite sure I foolw what you want given your example.

To expand your example, If MULTI was in column G, just under TELCEL, and it met your other criteria (columns L and Q), would that get a count of 2 (second occurence or TELCEL OR MULTI, or would it get a count of 1, since it is the first occurence of MULTI (each counted separately)?

Also, for the criteria of the date is column L and the Venta in column Q, is that criteria coming from rows 3 and 4 where TELCEL and MULTI are?

 

[Sirmarte]

Hello Joyner,
That´s correct! in column G are multiple rows some contains TELCEL, MULTI or #blankcells those has to met criteria on columns L and Q, they must be counted separately from 1 to... If finds TELCEL starts counting from 1 to any no matter if find MULTI couse MULTI has to be counted from 1 to any separately from TELCEL.
Criterias in Column L and column Q are the info I get of the main system, I mean I download that info from the system and if a TELCEL/MOVI appears in a row, for example TELCEL appears in row 17, you must take data of Column L and column Q "ROW17" that´re already there..
Sorry but I cannot unpload a file whit excel...
THKS in ADV!

 

[Joyner]

Hello, I didn't quite follow this: "for example TELCEL appears in row 17, you must take data of Column L and column Q "ROW17" that´re already there." For my formulas, I assumed that the formula needs to look in those two columns for the criteria in cells L3 and L4 and Q3 and Q4, for TELCEL and MULTI respectively. If that is wrong, let me know. See the link at the very bottom for an example file I used. If I understand correctly, use this formula starting in row 5, column F, and copy down:

=IF(G5="","",IF(G5="TELCEL",COUNTIFS($G$5:G5,"TELCEL",$L$5:L5,$L$3,$Q$5:Q5,$Q$3),COUNTIFS($G$5:G5,"MULTI",$L$5:L5,$L$4,$Q$5:Q5,$Q$4)))

If you want to get the total count for TELCEL and MULTI, put this formulas in cells F3 and F4:

=COUNTIFS($G$5:G22,"TELCEL",$L$5:L22,$L$3,$Q$5:Q22,$Q$3)
=COUNTIFS($G$5:G22,"MULTI",$L$5:L22,$L$4,$Q$5:Q22,$Q$4)

Not I used a test range going to row 22, so change for your actual range.

https://www.dropbox.com/s/5w0ywkbk65d8sk5/COUNTIFS.xlsx

 

[Sirmarte]

Hi joyner!

You understood correctly!! Actually I used the tips u gave me on your countifs formula... that helps me lot! I appreciate your time to help me!
Thanks!

Sirmartell