CountIF

[OddProject]

Hi guys, easy one for you.

I have 4 colums:

the 1st I have a unique code.
the 2nd i have the date log
3rd a colum with 4 types (red, blue, green etc) for example.
4th just a title.

I need to count the number of colours in colum 3 in each month (Aug, sept, oct, Nov, Dec, Jan and Feb.

I know i need a nested formula = countIf(C2,C134,"Blue") Where B =>01/08/2009 AND =<30/08/2009.



Get what I mean?

Many thanks

 

[qafself]

Check out SUMPRODUCT

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[OddProject]

I tried: =SUMPRODUCT((C2:C133="Blue")*(B2:B133="Aug"))

but results in 0... Which is impossible. Could you please provide an example.

Thanks

 

[OddProject]

I also want to keep my dates (Without having to change 13/08/2009 to "Aug"). So i'll need a nested:

Count "Blue" in column C2:C134
Where B2:B134 >=01/08/2009 and <=30/08/2009

which hopefully results in 9 entries found.

 

[OddProject]

Here's some mock data to help support my request.

I hope you get the idea

 

[OddProject]

Here's some mock data to help support my request.

I hope you get the idea

 

[OddProject]

Here's some mock data to help support my request.

I hope you get the idea

 

[Brianwarnock]

OK lets try again and see if this site works

Not sure you can enter dates directly in a formula in Excel,
I would add a cilumn =Month(B2) etc then use that
Also, I assume this was mentioned in the Link, as all of your arrays are comparisons you will need to use something like the double unary to do the maths, I forget the Tech explanation terms, so if G is the new column

=sumproduct(--(G2:G133=8)*(c2:c133="Blue"))

Brian

 

[OddProject]

Hi Brian thanks for the help,

I have tried various methods, but when I use "=sumproduct(--(G2:G133=8)*(c2:c133="Blue"))" I always end up with the value of '0'...

I'll even attach my spreedsheet, I have no idea why this is happening.

thanks again,

OddPro

 

[OddProject]

Here is the Example

 

[OddProject]

P.s The format of the dates are not important, I need summarised monthly data but I have also tried Aug, Sept etc... Aug-09, Sept-09 etc... 01/08/09, 14/09/2009, etc... And now month numbers 8, 9, etc...

 

[qafself]

How about something like this in summarised!B2
=SUMPRODUCT(--(Data!$B$2:$B$133>=Summarised!$B$1)*(Data!$B$2:$B$133<Summarised!$C$1)*(Data!$C$2:$C$133=A2))

This sums the occurances where the date is greater than or equal to the date in the col head and the date is less than the col head of the next col and the colour is blue

Adjust values for other months

 

[qafself]

If you are using 2007 you could use COUNTIFS instead of SUMPRODUCT

 

[OddProject]

How about something like this in summarised!B2
=SUMPRODUCT(--(Data!$B$2:$B$133>=Summarised!$B$1)*(Data!$B$2:$B$133<Summarised!C1)*(Data!$C$2:$C$133=A2))

This sums the occurances where the date is greater than or equal to the date in the col head and the date is less than the col head of the next col and the colour is blue

thanks for the reply, but im not too sure if I understand... Could you chuck an example into a spreedsheet?

P.s Did you look at the sample data I provided? All I need is a count per month.

Thanks again,

OddPro

 

[qafself]

see attached

 

[OddProject]

Brilliant mate, exactly what I need.

Thank you muchly!

OddPro

 

[Brianwarnock]

Hi Brian thanks for the help,

I have tried various methods, but when I use "=sumproduct(--(G2:G133=8)*(c2:c133="Blue"))" I always end up with the value of '0'...

I'll even attach my spreedsheet, I have no idea why this is happening.

thanks again,

OddPro

In your spreadsheet the month was in Col E not G


Nice one Gafself


Brian

 

[qafself]

No Problem Oddpro - thanks for letting me know you are now sorted out.

Brian - you need more practice with SUMPRODUCT - I seem to remember I introduced you to the beauties of it several years ago........

 

[Brianwarnock]

No Problem Oddpro - thanks for letting me know you are now sorted out.

Brian - you need more practice with SUMPRODUCT - I seem to remember I introduced you to the beauties of it several years ago........

What I suggested works, but as for more prectice, I'm retired 4 years next week, why should I.

Brian

 

[qafself]

Yes - you're right, retirement is sweet -I get my bus pass this year and a bit more pension!