count items in column B if month of date in column A is X

[notwen]

Hi,

I'm getting very confused with countif(s) and month() and the possible use of Sum() or Sumproduct(). I would just like to be able to count the number of times a particular number comes up in column B if the month of the date (dd/mm/yyyy) in column A is, say, January.

Help, please!

Thank you for any guidance.

Cheers,
notwen

[Fotis1991]

Hi

Try this.

=SUMPRODUCT((MONTH(A4:A100)=1)*(B4:B100=5))

1=January 5=the number that you are looking for.

Hope to helps you.

[OnErrorGoto0]

Just in case:

A blank cell will return 1 from the MONTH function, so I would suggest

=SUMPRODUCT((MONTH(A4:A100)=1)*(A4:A100<>"")*(B4:B100=5))

[notwen]

Thank you both. However, and sorry for not having put this in earlier, that the columns are found on another worksheet (in the same file). I didn't think that it would make a difference, but it seems to. The formula above doesn't work. I get a "#VALUE!" error.

I have:

=SUMPRODUCT ((MONTH(OtherTab!B:B)=1*(OtherTab!B:B<>"")*(OtherTab!C:C=5)))

Ideas?

Thanks a bunch,
notwen

[OnErrorGoto0]

=SUMPRODUCT ((MONTH(OtherTab!B:B)=1)*(OtherTab!B:B<>"")*(OtherTab!C:C=5))

Parentheses in the wrong place.

[Haseeb Avarakkan]

You can use TEXT function to avoid the blank cell issue like,

=SUMPRODUCT(--(TEXT(A1:A100,"m;;;")="1"),--(B1:B100=5))

custom format "m;;;" will ignore all text, negative & blank cells.

[notwen]

Sorry, guys. Thanks for your efforts but it just won't work. I keep getting the VALUE error.

Instead, on the data tab, I've created a column where I put the month pulled from the date. It isn't clean because it means that I have to remember to add that calculation whenever a new line item is added. Yes, I remembered to take into account the blank cell returning a "1". I use IF(ISBLANK($B5),"",MONTH($B5)).

Could someone create a very simple spreadsheet with the formula above that I could use to see it actually work? Please remember to have the summary (the formula) on a separate tab with the data on another. That's the way I'm set up.

Thanks again,
notwen

[OnErrorGoto0]

Just to be sure - you are using 2007 as your profile states? In 2003 and earlier you cannot use entire column references in a SUMPRODUCT (or other array) formula.

[Fotis1991]

Hi

Take a look to the sample.


Note:

There is a mistake in the highlight color of the sample. Yellow color must be Row 18, NOT 19!!

[notwen]

Hi Fotis,

Thank you for the example. It does work!
I also tried it using another sheet for the formula and it works, too!

OEGO: I am on Excel 2007 (after a moment of panic, I checked).

Ok, now I have to see why my attempt doesn't work.

Thanks to all.

Cheers,
notwen

[Fotis1991]

You are welcome

Pls mark your thrad, as SOLVED.

[OnErrorGoto0]

Do you have any error values in the columns you are checking?

[notwen]

Hi guys,

Thanks for taking the time to answer.

In the end, although I did get it to work - a colleague fiddled with it and chose to ignore some syntax error indications, I had to completely change my method of data gathering. The SUMPRODUCT function simply takes too long to calculate and given that I had a table of these to do, I chose to go to separate monthly files (so as not to have to look for the month).

But, SOLVED.

Thanks, again.
Notwen