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:

	Value 1	Value 2	Value 3	Value 4	Value 5
Monday	appears 5 times on Monday
Tuesday	appears 10 times on Tuesday
Wednesday
…

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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 !