• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. H

    Reverse Countifs

    Try, =IF(AND(Z10="Yard Zone",COUNTIFS(Y11:Y$98,Y10,Z11:Z$98,"Yard Zone")=0),Z10,"") Note: There are relative & absolute reference.
  2. H

    Formula automatically get changed with change of month

    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)))
  3. H

    how to subtract only the different cells from a row

    Attached is your sample file. Yellow highlighted is the formula.
  4. H

    how to subtract only the different cells from a row

    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)),"")
  5. H

    Split text after the /

    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 /
  6. H

    Vlook up closed workbook

    Hello, INDEX/MATCH combination will work between closed workbook.
  7. H

    Create unique code based on given string

    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...
  8. H

    Sum of Particular product in particular month

    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)
  9. H

    COUNTIFS with multiple array

    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...
  10. H

    Generate summary report from data using formula

    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...
  11. H

    Unable to Combine IF and Vlookup function with multiple Variables

    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...
  12. H

    Generate summary report from data using formula

    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))
  13. H

    Wishing a very happy birthday to Haseeb A !

    Thank you Thomas & Khalid. All have a nice day!
  14. H

    Count Unique Numbers in a List

    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)))
  15. H

    How to sum the content of a cell

    ...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.
  16. H

    Sum of negative values for variable number of columns

    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))
  17. H

    Wishing a very happy birthday to Haseeb A !

    Thank you dear!
  18. H

    Report by month based on date

    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
  19. H

    Need to allow for two option

    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...
  20. H

    Problem Lookup

    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...
  21. H

    Mark matched strings in one column

    Hello, May be... =IFERROR((MMULT((INDEX(A2:B$130000,MATCH(C1&"*",C2:C$130000,0),)=A1:B1)+0,{1;1})=2)+0,"")
  22. H

    Need help for SUMPRODUCT

    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))
  23. H

    Count non-blank cells in Excel rows using a formula (not using VBA)

    Hello, If you want to count ONLY numbers which are >0 or <0, then =SUM(COUNTIF(N1:N12,{">0","<0"}))
  24. H

    Sum Finance year - based on current date

    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 *
  25. H

    formula for multiple conditions

    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)
Back
Top