Formula to sum up by date

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Formula to sum up by date

Post by adam »

Hi anyone,

The following formula does exactly what I'm in need of.

However, to make it easier for the user I want the formula to count data when the user writes the date in cell A1 of the active sheet instead of having to write the date in the formula.

Any help on this would be kindly appreciated.



=COUNTIFS(C:C,">=" & DATE(2021,10,31),C:C,"<=" & DATE(2021,10,31),D:D,"Washington",C:C,"<=" & DATE(2021,10,31),E:E,"OLD",C:C,"<=" & DATE(2021,10,31),F:F,"<5",C:C,"<=" & DATE(2021,10,31),G:G,"APPLES")
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula to sum up by date

Post by HansV »

Replace all occurrences of DATE(2021,10,31) in the formula with A1
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Formula to sum up by date

Post by adam »

=COUNTIFS(C:C,">=" & DATE(A1),C:C,"<=" & DATE(A1),D:D,"Washington",C:C,"<=" & DATE(A1),E:E,"OLD",C:C,"<=" & DATE(A1),F:F,"<5",C:C,"<=" & DATE(A1),G:G,"APPLES")

Its giving me you've entered too few arguments for this function.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula to sum up by date

Post by HansV »

Please read my previous reply again.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Formula to sum up by date

Post by adam »

Great. It worked. if Im taking the data range from another sheet to the active sheet where should I put the sheet name in the formula?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula to sum up by date

Post by HansV »

Instead of A1, use

SheetName!A1

if the name of the sheet has no spaces, or

'Sheet Name'!A1

if it contains spaces.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Formula to sum up by date

Post by adam »

Not the sheet that has the date. But the sheet from where data is taken to the sheet where date is present.

The sheet where the columns C, D, E, F and G exists.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula to sum up by date

Post by HansV »

Then change C:C to SheetName!C:C or 'Sheet Name'!C:C. etc., substituting the real name of the sheet, of course.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Formula to sum up by date

Post by adam »

Thanks for the help Hans.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Formula to sum up by date

Post by adam »

I want the formula to count all the cities except Washington, New York & Philadelphia. At present it counts all except Washington. How I may achieve this?

=COUNTIFS(C:C,">=" & DATE(A1),C:C,"<=" & DATE(A1),D:D,"<>*Washington*",C:C,"<=" & DATE(A1),E:E,"OLD",C:C,"<=" & DATE(A1),F:F,"<5",C:C,"<=" & DATE(A1),G:G,"APPLES")
Best Regards,
Adam

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Formula to sum up by date

Post by StuartR »

Did you try simply adding
D:D, "<>*New York*",
and
D:D, "<>*Philadelphia*",
to your formula, after the existing
D:D,"<>*Washington*",
StuartR