Using MONTH to SUMPRODUCT keeps using all blanks as January

**amerandstarbuck** · 06-19-2018, 10:48 AM

Hi Guys, new here, please be gentle with me

I am trying to sum the total from a column based on MONTH range. But counting the occurrences within a column the result keeps adding all blank cells as January.
The formula I am using is this:
=SUMPRODUCT(--(MONTH($B$6:$B$500)=1))

The spreadsheet is column B - a list of dates 01/06/18, 02/08/18, 15/01/19
If there's only 3 entries and I want to calculate those appearing in January (MONTH 1), it keeps coming up as 498 (the length of all the blanks)
I have already tried using an IF function in a separate column to show as 0 but this has not helped.

How do I stop this from happening please? I know this is a common thread but I couldn't find the answer to help me out.

Your time is much appreciated

**Special-K** · 06-19-2018, 11:08 AM

Its probably defaulting a blank cell as 01/01/1900 hence January month

Try

=SUMPRODUCT((MONTH($B$6:$B$500)=1)*($B$6:$B$500<>""))

Where Month = 1 and B6:B500 is not blank

**amerandstarbuck** · 06-21-2018, 09:23 AM

thank you for your help and time, it is much appreciated

Using MONTH to SUMPRODUCT keeps using all blanks as January

LinkBack

Thread Tools

Rate This Thread

Display

Using MONTH to SUMPRODUCT keeps using all blanks as January

Re: Using MONTH to SUMPRODUCT keeps using all blanks as January

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Averaging from January to current month

Unabbreviate Month ('Jan' to 'January')

[SOLVED] Display month name orde month (1,2,3....12)->(January, February,..December) in PivotTable

Only include Month of January in sumproduct function on another page using array

Problems with the month on January..

returning value if month is January

[SOLVED] using date function, month shows as January when i type (12)

Bookmarks

Bookmarks

Posting Permissions