If a cell contains any text then add a value (with dates)

Solved/Closed

ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017 - Mar 4, 2017 at 07:22 PM
ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017 - Mar 7, 2017 at 07:01 PM

I am looking for help with a formula that will allow me to add up the number of times a certain date is a Monday. I am using =COUNTIF(A1:A30,"*Monday*"). The issue that I have is I am entering dates like 3/6/17 and have the formatting set to show it as "Monday, March 7, 2017." Because Monday was not typed into the cell, it doesn't count it. Is there a way to get the number of Mondays counted?? Please help.

Numbers, text, dates, or times of day that you type into a cell.
Excel if cell contains date then add 1 - Best answers
If cell contains text then return value in another cell - Best answers
How to type ' on keyboard - Guide
Keyboard won't type - Guide
Types of whatsapp - Guide
You have guessed too many times ✓ - WhatsApp Forum
Time set day - Guide

2 responses

Answer 1 / 2

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 4, 2017 at 08:27 PM

ryannordgren, Good evening.

Try to use:

=SUMPRODUCT(--(WEEKDAY(A1:A30, 2)=1))

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017
Mar 4, 2017 at 11:32 PM

Yes, Thank you! This works.

ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017
Mar 5, 2017 at 12:04 AM

One more question, if you have the time. I can get this to work now for each day of the week except for Saturday. Any idea how to get it to count Saturdays using this same formula?

Answer 2 / 2

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 5, 2017 at 08:31 PM

ryannordgren, Good evening.

"...Any idea how to get it to count Saturdays using this same formula?..."

Look at this:

WEEKDAY( Serial_number, Return_type)

The WEEKDAY function syntax has the following arguments:

Serial_number:
A sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type:
A number that determines the type of return value.

2 --> Numbers 1 (Monday) through 7 (Sunday).

then...
1 --> Monday
2 --> Tuesday
3 --> Wednesday
4 --> Thursday
5 --> Friday
6 --> Saturday
7 --> Sunday

In the suggested formula, we compare the number of the day of the week to the value 1, which means Monday.
=SUMPRODUCT(--(WEEKDAY(A1:A30, 2)= 1 ))

Simply replace these values to get the count for each day of the week you want.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017
Mar 7, 2017 at 03:14 PM

I appreciate your help. This formula:
=SUMPRODUCT(--(WEEKDAY(A1:A30, 2)= 1 ))
works for every day of the week except Saturday. For some reason when I enter the formula: =SUMPRODUCT(--(WEEKDAY(A3:A200, 2)= 6 )) - for Saturday, it shows the value "198" with no values in the A column!? For every date I add in the A column, it subtracts another 1 from the 198. For example if I add a date range comprising Sunday through Saturday - 7 lines - it will read "192."

The formulas counting for every other day of the week read "1" for this same formula. It just doesn't want to work for Saturday for some reason. I don't know why it is subtracting when the same formula using every other day of the week (1,2,3,4,5,7) works correctly, adding a value of 1.

Any ideas friend?

x > ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017
Updated by x on 7/03/17 at 05:57 PM

Try this one. It checks for a blank in the cell.

=SUMPRODUCT(--(A3:A200<>""),--(WEEKDAY(A3:A200,2)=6))

This issue of blanks being interpreted as Saturdays has been around for a years now.

ryannordgren Posts 7 Registration date Saturday March 4, 2017 Status Member Last seen March 7, 2017 > x
Mar 7, 2017 at 07:01 PM

Most excellent! that works perfectly!

Similar discussions

Excel if range of cell contain a text put a cell value in

If a cell contains any text then add a value

Highlight cell if another cell contains any text

Formula for if cell contains word, assign value for multiple

Excel: If Date =, then enter a value

Match partial text string in one sheet to another text string in another sheet

Similar discussions

Subscribe To Our Newsletter!