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.
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
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....
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
Sorry - got it.
=SUMPRODUCT(--(WEEKNUM(Sheet1!$E$3:$E$4000+0)=A8),--(Sheet1!$H$3:$H$4000<>""))
Re: SUMIFS and WEEKNUM
QuoteThe 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;670274If 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.
Don’t have an account yet? Register yourself now and be a part of our community!