SUMIFS and WEEKNUM

  • I would like to do this:


    IF WEEKNUM of Sheet1!E:E = A5
    THEN SUM Sheet1!H:H


    So far I have this:



    =SUMIFS(Sheet1!H:H,WEEKNUM(Sheet1!E:E,1),A8)


    It isn't working.


    Please help. Thank you.

  • Re: SUMIFS and WEEKNUM


    When you are converting a range on the fly, you can't use SUMIF or similar functions.


    alternatives:


    1. =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100)=A8),Sheet1!$H$2:$H$100)


    Note: with Sumproduct it is not advised to use whole column references due to performance lag...


    2. If you need full columns or large columns or experience lag, then add a helper column in Sheet1 to extract the WEEKNUM for each cell in column E, then use SUMIF


    e.g. =SUMIF(Sheet1!X:X,A8,Sheet1!H:H)


    where Sheet1, column X contains formula like : =WEEKNUM(E2) copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIFS and WEEKNUM


    Quote from NBVC;670204

    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100)=A8),Sheet1!$H$2:$H$100)


    I don't believe that formula will work (certainly not in Excel 2007) because WEEKNUM function can't handle ranges - in Excel 2007 and later, though, it can handle arrays so you can use +0 to convert like this:


    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$2:$E$100+0)=A8),Sheet1!$H$2:$H$100)

  • Re: SUMIFS and WEEKNUM


    Ah.. shoot! I forgot about that... should have done a quick test.... thanks for notifying me.


    Anyway, I think the second option with helper and Sumif is better option....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIFS and WEEKNUM


    Excellent. That worked very well. Thank you. Decided to go with:
    =SUMPRODUCT(--(WEEKNUM(Sheet1!$E$3:$E$1000+0)=A8),Sheet1!$H$3:$H$1000)
    The problem with the other method is that helper column. When new rows are added I would need to drag the formula down again - is that right? I'm using Office 2010.


    Secondary question - do you know how I'd do the same thing with count rather than sum? (I'm not familiar with sumproduct, as you can tell)

  • Re: SUMIFS and WEEKNUM


    Quote

    The problem with the other method is that helper column. When new rows are added I would need to drag the formula down again - is that right? I'm using Office 2010.


    If you convert the data range into an Excel Table all formulas and formatting will automtically extend to new rows that are added to the table. Plus, you get the benefit of using structured references in your formulas.

  • Re: SUMIFS and WEEKNUM


    Quote from AAE;670274

    If you convert the data range into an Excel Table all formulas and formatting will automtically extend to new rows that are added to the table. Plus, you get the benefit of using structured references in your formulas.


    Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!