入黎免費幫你砌excel表 / 教你formula (6)

咁睇你接唔接受到我講既方法
管理上會易d

係呀
可以點整？thanks

佢個方法好好喎
俾我寫都係咁上下
你而家係run唔到?

條formula 用緊 {4,11}

佢係3月最後一個Monday 開始轉
10月最後一個MONDAY 又轉返

睇唔明條formula
唔知要點改

其實可以係formula度highlight咗再㩒F9就知佢計緊乜
當然你copy出嚟睇都得，但array formula係舊版excel唔會睇得到

我自己做一次就咁
其實到最後都真係同佢果條差唔多樣
但我估佢係再optimize得好少少
你見我果條係累贅過佢少少

佢{4,11}唔代表咁就有問題
因為佢day入0或者負數咪可以番番去10月
應該都係optimize既原因先寫成咁

如果你都係唔明
其實可以用番尋常方法做
先計4月第一個星期日係幾時 (date1)
再計10月第一個星期日係幾時 (date2)
check input係唔係 > date1 && < date2

Thanks

試吓你嗰個先

我意思係我搵到嗰條formula
係3月同10月最後個monday轉

係唔知點由last Monday 轉做first Sunday

原來佢果條係last monday

sorry for 1999

怪得知佢同我唔同樣
不過你check下岩唔岩先

4月嗰個ok

10月好似遲咗1日
今年10月3日星期日
Formula 嗰度10月4先識轉

試下改成


淨係多左 +{0,1}

想問下點樣block 左cell唔比輸入
如果C2果格打左"不正確", 咁D2 同 E2 唔可以入資料

想問VBA countif 係唔係食唔到兩個range?係咁出mistype error

Sub Countif_Example2()

  Dim ValuesRange As Range
  Dim ResultCell As Variant
  Dim CriteriaValue As Range

  Set ValuesRange = Range("A1:A10")
  Set CriteriaValue = Range("C3:L3")

  ResultCell = WorksheetFunction.CountIf(ValuesRange, CriteriaValue)
ResultCell=sumproduct(ResultCell)
 
End Sub

我想做
=sumproduct (countif(rng1,rng2))
但又唔想用evaluate 大師有無計。

佢個type應該要係value?
我估你唔可以好似array formula咁塞個array入去
可能要將criteriaValue逐個數countif再拆番個array出黎

perfect

你試下咁work唔work

peter sir 搞左成晚唔係幾run到你話for loop個range

我要做既係咁：
rng1:[1,2,3,4,5]
rng2:[1,2]

sumproduct (countif
出到個integer係2就execute一堆function.

幫幫手

真係work, 無限感激, 慳左我好多時間

同埋留意全世界咁多國家，轉daylight savings都唔係同日子轉
記得揀番啱你要嘅rule

不如自己寫算啦

Dim CheckCell As Cell
Dim CriteriaCell As Cell
Dim rowCounter As Integer
Dim TotalCount As Integer

TotalCount = 0
For each CheckCell in Range(“A1:A10”)
    For each CriteriaCell in Range(“C3:L3”)
      If CheckCell.Value = CriteriaCell.Value Then
         TotalCount = TotalCount + 1
         Exit For
      End If
    Next CriteriaCell
Next CheckCell

我呢個淨係count總共有幾多個A1:A10既cell value match with C3:L3其中一個

唔熟worksheet function唔係幾知sum product 要做乜

Sub Countif_Example2()

    Dim ValuesRange As Range
    Dim ResultCell As Integer
    Dim CriteriaValue As Range
    Dim Cell As Range
    
    Set ValuesRange = Range("A1:A10")
    Set CriteriaValue = Range("C3:L3")
    
    For Each Cell In CriteriaValue.Cells
        ResultCell = ResultCell + WorksheetFunction.CountIf(ValuesRange, Cell.Value)
    Next Cell
    
    MsgBox ResultCell
    
End Sub

用你d code再改少少就得
MsgBox 自己del, 比你睇返個數only

BTW其實睇到個WorksheetFunction.CountIf係食1個range+1個數再return double
你塞2個range比佢, 所以會有mistype error