Count values on one column that meet criteria in an other

[verborgh]

Hi all

I have an issue in excel, hope you can help me with it:

I have a number of values in one column (A), from 1 - 5. This is a code i gave certain objects.
In another column (B) i have different days of the week, not chronologically listed or anything.

I wish to track how many times a certain value from A happens on a certain day in B

So my column would look like this:

[TABLE="width: 565"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]appears 5 times on Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]appears 10 times on Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Wednesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I hope you can help me guys

Bests
verborgh

 

[AlphaFrog]

Try this formula in E2 where E1 is the Value 1 and D2 is the weekday Monday. Your data is in A2:B100
=SUMPRODUCT(--($A$2:$A$100=E$1),--($B$2:$B$100=$D2))

Or use the COUNTIFS function for Excel 2007 or later
=COUNTIFS($A$2:$A$100,E$1,$B$2:$B$100,$D2)

 

[verborgh]

Try this formula in E2 where E1 is the Value 1 and D2 is the weekday Monday. Your data is in A2:B100
=SUMPRODUCT(--($A$2:$A$100=E$1),--($B$2:$B$100=$D2))

Or use the COUNTIFS function for Excel 2007 or later
=COUNTIFS($A$2:$A$100,E$1,$B$2:$B$100,$D2)

It worked, i will always be in your debt !