Counting Data Based on Multiple Criteria

[Redlad91]

Hi,

I was wondering if i could get some help on a formula that i am looking to get up and running, I have tried Countifs and Index but seem to be having no luck in getting a result back, MY dilemma is based on two sheets, The first being just raw CSV data (this is what i want the formula to look at) and the output table (This is where i want it to show the number of results found).

What i am trying to find is an overall count of how many incidents which were assigned to a certain person on a certain day, using the two tables below;

Data table
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Incident[/TD]
[TD]Name [/TD]
[TD]Date Opened[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]INC0001[/TD]
[TD]Ben [/TD]
[TD]26/06/2019[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]INC0002[/TD]
[TD]Bradley [/TD]
[TD]25/06/2019[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]INC0003[/TD]
[TD]Tom[/TD]
[TD]26/06/2019[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]INC0004[/TD]
[TD]Ben[/TD]
[TD]26/06/2019[/TD]
[/TR]
</tbody>[/TABLE]

Output Table
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Ben[/TD]
[TD]Bradley[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25/06/2019[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]26/06/2019[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

MY formula i previously used but TNA was:

=Countifs('Data Table'!B:B,'Output Table'!B1, 'Data Table'!C:C, 'Output Table'!A2)

Let me know if anyone has any questions, Will be happy to help.

Thanks

 

[Special-K99]

You always want to compare against Row in the output table - ALWAYS - so you need to anchor the reference when comparing against the names
Same with dates

Try (this assumes you have header names in row 1 and dates in column 1

=Countifs('Data Table'!B$:B$100000,'Output Table'!B$1, 'Data Table'!C$2:C$100000, 'Output Table'!$A2)

It's not econimcal to use whole ranges, e.g. A:A or B:B, put a row number in that will cover the complete range of data, e.g. A1:A100000

 

[Redlad91]

Hi, THanks for the reply, I tried previously with the anchors but still coming back with 0 even though i know there are results which should be counted. I tried the formula you posted above but still TNA.

Could this be if the date on the left of the table has a formula within the output table? So i have set it up so there is always todays date at the top, and down the left automatically updates and add's a date dependant on this first cell, for example below;

Output Table
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Todays date[/TD]
[TD]26/06/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Date[/TD]
[TD]Ben[/TD]
[TD]Bradley[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A5-1 (25/06/2019)[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=B1 (26/06/2019)[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Other than that i am not sure what else could be causing the formula not to work, Potentially that my data is CSV? would it be better as normal data, or could it be something to do with the formatting of the cells?

Thanks

 

[Special-K99]

Isolate the problem, take out one of the conditions and see what values you get. You'll have to manually count and compare results but it will give you an idea of whether the date comparison is failing or the name comparison.

Failing that, upload the file to an online storage site and post a link here though moderators prefer people don't do that.
Without examining the data it's gonna be difficult to solve.