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")
Formula to sum up by date
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Formula to sum up by date
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to sum up by date
Replace all occurrences of DATE(2021,10,31) in the formula with A1
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Formula to sum up by date
=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.
Its giving me you've entered too few arguments for this function.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Formula to sum up by date
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
Adam
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to sum up by date
Instead of A1, use
SheetName!A1
if the name of the sheet has no spaces, or
'Sheet Name'!A1
if it contains spaces.
SheetName!A1
if the name of the sheet has no spaces, or
'Sheet Name'!A1
if it contains spaces.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Formula to sum up by date
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.
The sheet where the columns C, D, E, F and G exists.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to sum up by date
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
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Formula to sum up by date
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")
=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
Adam
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Formula to sum up by date
Did you try simply adding
D:D, "<>*New York*",
and
D:D, "<>*Philadelphia*",
to your formula, after the existing
D:D,"<>*Washington*",
D:D, "<>*New York*",
and
D:D, "<>*Philadelphia*",
to your formula, after the existing
D:D,"<>*Washington*",
StuartR