Re: Auto fill dates
Hi
It's better to use a nemed range for your holidays. Then.
In A2 and copy down.
Code
=IF(WORKDAY(A$1-1,ROWS(A$1:A1),Holidays)>EOMONTH(A$1,0),"",WORKDAY(A$1-1,ROWS(A$1:A1),Holidays))Posts by Fotis1991
-
-
Re: Help for Countif with 30 day Date Range situation.
Cross posting here..
-
Re: Extracting 2nd Word from cell
Try
=TRIM(MID(A1,LEN(LOOKUP(9^9,LEFT(A1,COLUMN(1:1))*1))+1,2^15))
-
Re: Compare 2 List and extract data in 3rd
Nice pictures, but a small sample workbook will be more usefull!
-
Re: Compare 2 List and extract data in 3rd
...........................................:smile:
-
Re: Formula to calculate number of working days left in month
With =today() in A1(or replace the A1 with today() in the formula) try in another cell, this ARRAY formula.
=EOMONTH(A1,0)-A1+1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&EOMONTH(A1,0)-A1+1)))=1,1,0))
-
Re: Compare 2 List and extract data in 3rd
Take a look to this.
-
Re: More Sum If Help Please
Quote from Zpichette;681814Perfect!!
Can you explain to me what this formula is doing? how does it know to only add the last column if you have so many arrays im not familiar witht he sum product function.
=SUMPRODUCT(($A$2:$A$20="mex")*($B$2:$B$20>0)*($B$2:$B$20))
In simple words, formula says:
Look in range A2:A20 for the string "mex"(($A$2:$A$20="mex")). If you find some results, then look in range B2:B20 and see in which rows (that in column A contains "mex") the number is > from 0($B$2:$B$20>0). Then add only these values(($B$2:$B$20)).
Bob's Philips site is an excellent drive to learn everything for SUMPRODUCT.
-
Re: More Sum If Help Please
=SUMPRODUCT(($A$2:$A$20="mex")*($B$2:$B$20>0)*($B$2:$B$20)) :question:
-
Re: More Sum If Help Please
Post#4 suggestion?
-
Re: More Sum If Help Please
Perhaps.
=SUMPRODUCT(($A$2:$A$20="mex")+($A$2:$A$20="usa")*($B$2:$B$20))
-
Re: Excel Formula to count #of M on Weekends
Now is(for sure) an issue for Pike!:surfing::surfing: I have no idea about VBA!
-
Re: Excel Formula to count #of M on Weekends
1 way..
=COUNTIFS($B$2:$O$2,"Sat",B3:O3,"M")+COUNTIFS($B$2:$O$2,"Sun",B3:O3,"M")
-
Re: VLOOKUP returning wrong value
I think now it's clear.
Use this ARRAY formula.
=INDEX(Sheet4!$C$2:$C$100;MATCH(A2&B2;Sheet4!$A$2:$A$100&Sheet4!$B$2:$B$100;0))
-
Re: VLOOKUP returning wrong value
One more try...
In which column do you want your result? Which column(s) must we use for Match?
Give us an example in which VLookup(that you use) does not gives the correct results.
-
Re: VLOOKUP returning wrong value
Can you explain your goal using the columns? COLUMN a, COLUMN b...AND SO ON..PLS?
-
Re: VLOOKUP returning wrong value
Take a look to this.
-
Re: VLOOKUP returning wrong value
...................................
-
Re: VLOOKUP returning wrong value
In B2 and copy down try using INDEX & MATCH.
=INDEX(Sheet2!$A$2:$A$100,MATCH(A2,Sheet2!$B$2:$B$100,0))
=iferror(=INDEX(Sheet2!$A$2:$A$100,MATCH(A2,Sheet2!$B$2:$B$100,0)),"")
-
Re: Countif/Sum Product
@ etaf
Op has the solution that wants here..