# 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

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.MIKEmessage 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.MIKEhttp://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 Yearor European Date Style - Day Month Year?MIKEhttp://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?MIKEmessage edited by mmcconaghy

Report •

#5
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.MIKEmessage edited by mmcconaghy