View Full Version : Using Countifs & Weekdays

RickS650

05-03-2017, 08:36 AM

I have a worksheet that has a column of dates (let's call it A1:A100). The sheet has another column (B) that has values in some of the rows. I would like to calculate, in code, and without iterating through the sheet, how many instances of Mon (or any day of the week) have a value and, as a bonus, sum the values.

I have tried ApplicationFunction.Sum and .SumProducts and Countifs. Just stumped!l Any help would be appreciated.

Using Excel 2007.

I would like to calculate, in code, and without iterating through the sheet

Ya got me!

Why is that so critical?

I bet one of our Formula experts could write a cell formula that could do it. I can do that, as long as I use a helper column with a WeekDay function.

Helper Column (C) Formula ---- "=IF(WEEKDAY(A1,2)=1,1,0)"

Count of mondays formula -----"=SUM(C1:C16)"

Sum of Monday Values Formula "=SUMIF(C1:C16,1,B1:B16)"

RickS650

05-03-2017, 01:30 PM

Thanks for the quick response. I already have the cell formula but it got me thinking that there must be a sleeker way. Sum(-- is so close for the weekday bit but doesn't quite work.

There is probably a CSE formula, but they give me headaches.

RickS650

05-04-2017, 01:26 AM

Yes, I got a headache from CSE too. Strange thing is when I pressed CSE I got the #VALUE error, but when I pressed Enter I got a number, just doing the Weekday bit. F9 on parts of the formula produced results as expected. I may try to reproduce what I did and post it here.

I have now run the macro recorder on the weekday part and got the correct answer:

Sub Macro1()

'

' Macro1 Macro

'

Range("T2").Select

Selection.FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"

Debug.Print Range("T2")

' Result is 71 - correct

End Sub

but I would like to put the result into a variable (say x) but, of course, I can't because it is an array. This is now personal between me and this macro..... I WILL win!

Bob Phillips

05-04-2017, 04:47 AM

You don't need an array formula, SUMPRODUCT does it

=SUMPRODUCT(--(WEEKDAY(A1:A100)=2),B1:B100)

or

=SUMPRODUCT(--(TEXT(A1:A100,"ddd")="Mon"),B1:B100)

Bob Phillips

05-04-2017, 04:53 AM

I have now run the macro recorder on the weekday part and got the correct answer:

Sub Macro1()

'

' Macro1 Macro

'

Range("T2").Select

Selection.FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"

Debug.Print Range("T2")

' Result is 71 - correct

End Sub

You rarely need to select

Range("T2").FormulaArray = "=SUM(IF(WEEKDAY(RC[-2]:R[381]C[-2])=4,1,0))"

but I would like to put the result into a variable (say x) but, of course, I can't because it is an array. This is now personal between me and this macro..... I WILL win!

You can evaluate the formula

x = Application.Evaluate("=SUMPRODUCT(--(WEEKDAY(A2:A100)=2),B2:B100)")

RickS650

05-04-2017, 05:04 AM

By crikey, that's done it! Brilliant, I knew it could be done with a one-liner..

Thanks very much

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.