Solved How to Excel to Count Values in 1 Column Based on 2 criteria

January 6, 2016 at 13:34:05
Specs: Windows 7
Hi. I'm looking for some help on an excel problem.
[edit: apologies for the poor formatting, I've sorted that now]

i have a table like this:

  A        B         C               D          E
1 Date       Area   SettingMoved   From     To
2 01/01/16  Area1  X                  2        4
3 01/01/16  Area1  Y                  6        5
4 01/01/16  Area2  Z                  4        8
5 01/01/16  Area1  N/A  
6 01/01/16  Area1    
7 02/01/16  Area1   W                 4       8
8 02/01/16  Area2   V                 7       12

The bottom of the table is not fixed as I will be adding to it every day.

I need to collate the data so that it highlights how many settings were moved in each area on each day. I was aiming for a table with headings like this:

  A        B         C               
1 Date       Area1   Area2   
2 01/01/16    2         1  
3 02/01/16    1         1

I cannot work out which functions are best to use and in which order to collate the data in this way. I need the number of settings moved in each area per day.

Any help would be much appreciated!

message edited by E-D


See More: How to Excel to Count Values in 1 Column Based on 2 criteria

Report •


✔ Best Answer
January 6, 2016 at 16:27:39
With your data like this, see how this works:

I have used Column D to determine if a Setting has Moved or not. If this will not work for some reason let me know.

           A              B          C             D     E   
 1)       Date          Area    Setting_Moved    From    To
 2) January 1, 2016     Area1         X            2     4
 3) January 1, 2016     Area1         Y            6     5
 4) January 1, 2016     Area2         Z            4     8
 5) January 1, 2016     Area1         N/A                
 6) January 1, 2016     Area1                            
 7) February 1, 2016    Area1         W            4     8
 8) February 1, 2016    Area2         V            7     12
 9)          
10)
11)
12)       Date          Area1         Area2      Area3
13) January 01, 2016      2             1       
14) February 01, 2016     1             1       

In cell B13 enter the formula:

   =COUNTIFS($B$2:$B$8,B$12,$A$2:$A$8,$A13,$D$2:$D$8,">0")

Please note that the $ symbols are needed just as they are shown in the formula.

Drag down as many rows as needed,
then drag to the right as many columns as needed.

Any problems, let me know.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
January 6, 2016 at 14:07:30
I'm not sure what your trying to do, but as a start, read this HOW-TO which explains the use of the < PRE > tags to align your data.

http://www.computing.net/howtos/sho...

Please include Column Letters & Row Numbers.

We have an example of your before data,
so please show how you want the data to appear after.

MIKE

http://www.skeptic.com/


Report •

#2
January 6, 2016 at 14:35:23
Thanks for the feedback Mike. I've sorted that formatting now so hopefully it'll actually be readable and make sense to someone!

Cheers


Report •

#3
January 6, 2016 at 15:25:26
Just a quick question, which Date Style are you using
American Date Style - Month Day Year
or
European Date Style - Day Month Year?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
January 6, 2016 at 15:29:15
Second quick question, where is the Second Table going to be located?
On the same sheet as your data or a new sheet?
If on a new sheet, what will the sheet be named?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
January 6, 2016 at 16:27:39
✔ Best Answer
With your data like this, see how this works:

I have used Column D to determine if a Setting has Moved or not. If this will not work for some reason let me know.

           A              B          C             D     E   
 1)       Date          Area    Setting_Moved    From    To
 2) January 1, 2016     Area1         X            2     4
 3) January 1, 2016     Area1         Y            6     5
 4) January 1, 2016     Area2         Z            4     8
 5) January 1, 2016     Area1         N/A                
 6) January 1, 2016     Area1                            
 7) February 1, 2016    Area1         W            4     8
 8) February 1, 2016    Area2         V            7     12
 9)          
10)
11)
12)       Date          Area1         Area2      Area3
13) January 01, 2016      2             1       
14) February 01, 2016     1             1       

In cell B13 enter the formula:

   =COUNTIFS($B$2:$B$8,B$12,$A$2:$A$8,$A13,$D$2:$D$8,">0")

Please note that the $ symbols are needed just as they are shown in the formula.

Drag down as many rows as needed,
then drag to the right as many columns as needed.

Any problems, let me know.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •


Ask Question