Hello Neeraj,
Don't know your actual data setup. This works on the sample provided.
I5:
=VLOOKUP($H5,$A$4:$E$8,MATCH(I$4,$A$4:$E$4,0),0)
J5:
=SUM(INDEX($A:$A,MATCH($H5,$A:$A,0)):INDEX($A:$E,MATCH($H5,$A:$A,0),MATCH(I$4,$A$4:$E$4,0)))
Hello,
Assuming you are using Excel 2010 or higher. In N3, then copy across.
=IFERROR(INDEX(3:3,AGGREGATE(15,6,COLUMN($B3:$L3)/ISNA(MATCH($B3:$L3,$M3:M3,0)),1)),"")
Hello Sriram,
Here is one way:
=TRIM(MID(SUBSTITUTE("/"&A2,"/",REPT(" ",LEN(A2))),1*LEN(A2),LEN(A2)))
Change red highlighted to, 2 for 2nd, 3 for 3rd etc....for the instances of /
Hello Samto,
If you are using two character code, then you will not have unique code for each locations. You can make maximum only 36 code with two character.
Here is a quick try. This one still have issue with non alphabetic character. But at least give you some initiative.
In B2, then copy...
Hello Kutty,
Take a look on SUMIFS formula,
In G2:L2 enter first day in each month & format as "mmm-yy, then G3, copy down & across.
=SUMIFS($B:$B,$A:$A,">="&G$2,$A:$A,"<="&EOMONTH(G$2,0),$C:$C,$F3)
Hello JC,
if you have two arrays in the criteria field of a COUNTIFS/SUMIFS, one of the array must be in semi-colon (cannot use more than two). So use a semi-colon (;) to separate the 2nd array...
Hello Gaikwad,
Sorry for the late response.
Don't know about the structure of your file. using OFFSET to 161 columns might be performance issue.
One way:
=SUMPRODUCT(COUNTIFS(Data!$A:$A,$C2,Data!$B:$B,$A$2,OFFSET(Data!$C:$C,,COLUMN($A$1:INDEX($1:$1,161))-1),D$1))
Change red highlighted to...
Hello Mike,
Here is another way, even states in column J are not in order & always available under a group.
=VLOOKUP($C3,INDEX($J:$J,MATCH($B3,$I:$I,0)):INDEX($L:$L,2^20),2,0)
Change red highlighted column # CODE#
Or if states are not always available in a group...
Hello Gaikwad,
OFFSET is one of the powerful/flexible function & also a volatile.
Here is another way with OFFSET.
=SUM(COUNTIFS(Data!$A:$A,$C2,Data!$B:$B,$A$2,OFFSET(Data!$C:$C,,{0,1,2}),D$1))
Hello William,
If you have VALID numbers in Order Number, then you could use FREQUENCY function As this function will ignore unused cells, text & empty cells.
=SUM(SIGN(FREQUENCY(A:A,A:A)))
...or if you have number >9 between /
=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",50)),ROW(A1:A50)*50-49,50))))
Assuming maximum 50 separator in a cell.
Hello Thomas,
Try these.
Sum of -ve:
=SUMIFS(3:3,2:2,"<>Total",3:3,"<0")*(LOOKUP(99^99,3:3)<0)
For Total;
=LOOKUP(99^99,3:3)*(LOOKUP(99^99,3:3)<0)
Edit:
or with MIN,
=MIN(0,LOOKUP(99^99,3:3))
Hello,
If you just format "mmmm" the value still read as date not a text value for month.
One way is to use COUNTIFS to count the dates. Please see attached
No, FIND is case-sensitive. So If you use any wildcard FIND will look for EXACT text.
Not necessary in this circumstances. SEARCH will do a search in the text, so wildcard is not necessary, unless if you have multiple instances of texts.
eg: just imagine you have the following text
ABC FWU...
Or...
=IFERROR(VLOOKUP("Tax",INDEX(A:A,MATCH(IF(D3="",NA(),"*"&D3),A:A,0)):B1000,2,0),"Not Found")
Change B1000 to last unused cell.
The below version will look for last unused cell dynamically...
Hello,
Try this version in M8 & copy down.
=SUMPRODUCT((J$2:J$60="Special")*(F$2:F$60=K8),1/COUNTIFS(B$2:B$60,B$2:B$60,J$2:J$60,J$2:J$60,F$2:F$60,F$2:F$60))
Hello Prasad,
In E4 in Output,
=SUMPRODUCT(ISNUMBER(SEARCH(E$3,'Costing Delivery'!$A$18:$A$31))*('Costing Delivery'!$E$16:$AP$16=LOOKUP("zzzz",$E$2:E$2)),'Costing Delivery'!$E$18:$AP$31)
then copy across other cost & revenue. Note:use comma as in red highlighted instead of *
Hello DSP,
FYI. In order to get accurate answer with LOOKUP, currency column in table5 must be sorted in ascending order.
Try this version:
=SUMPRODUCT(C10:G10*SUMIF(Table5[Currency],C$9:G$9,Table5[Conversion Rate]))*IFERROR(VLOOKUP(B10,Table4[#Data],2,0),Sheet2!$C$34)