Different data in multiple cells

bluewaternavy

New Member
Joined
Feb 7, 2014
Messages
40
Hi, I am very new to using excel. I am trying to do a search (total) of the number of times M9 responded to Zone 5-U. Here is formula I came up with, but it coming back with errors. I over 36,000 calls to test, so this is just an example of course.

=COUNTIFS(N3:N32216,"M9",O3:O3221668,"5-U")
The "N's & O's" are the columns where the data is actually in the spreadsheet.


M822

<colgroup><col></colgroup><tbody>
</tbody>
9-U
M95-U
M8

<colgroup><col></colgroup><tbody>
</tbody>
8-U
M1515-R
M55-U
M95-U
M44-U
M95-U
M7159-U
M2055-U
M2155-U

<tbody>
</tbody>


Thanks for the help in advance.

 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Hi and welcome to the forum,

You're range for column O doesn't look right. Maybe try:
=COUNTIFS(N3:N32216, "M9", O3:O32216, "5-U")

If you still get errors can you provide more detail around exactly what they are, including the results you are getting vs what you are expecting.
 

bluewaternavy

New Member
Joined
Feb 7, 2014
Messages
40
Hey, looks like that worked. Thanks!!!! Do you know of a way to make the system add a number to say "6-U" instead of "5-U" when I drag to the next cell?
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
I'm not sure its clear what you mean? What is the current cell and which is the next cell - the one to the right or down one from the current cell?
 

bluewaternavy

New Member
Joined
Feb 7, 2014
Messages
40

ADVERTISEMENT

So I have to calculate M9 responding into every possible zone. So there are 19 zones (i.e., 1-U, 2-U, etc) I place the formula in the column labeled M2. Then the left hand rows are labeled 1-U, next one down is 2-U, etc. I would like to just put the formula in the first row and then drag down where it changes the 1-U to be 2-U, etc for each specific row.

So row one would be =COUNTIFS(N3:N32216, "M9", O3:O32216, "1-U")
Second row would be
=COUNTIFS(N3:N32216, "M9", O3:O32216, "2-U")

and so on.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
Try this in M2 and drag / copy down:

=COUNTIFS($N$3:$N$32216, "M9", $O$3:$O$32216, L2)

This assumes L2 has the value 1-U, L3 holds the value 2-U etc.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932

ADVERTISEMENT

Also, a different approach you can try is using a PivotTable:

-> Your data needs to have column headers
-> Select the range with data (including the column headers) and insert a PivotTable
-> Drag both column fields into the Row Labels area
-> Drag the second column field into the Values area

For an introduction to PivotTables, the following might help:
Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
Overview of PivotTable and PivotChart reports - Excel - Office.com
Using Pivot Tables and Pivot Charts in Microsoft Excel
 

bluewaternavy

New Member
Joined
Feb 7, 2014
Messages
40
So here is another set of data that I need to calculate. What I am trying to do is see how many responses a particular unit is responding too based on time of day parameters. So the data would be unit (column N), Time of Day (column D). All columns would be 3:32238 for values. Issue is this. The time of day is broken unit segments as listed below. How would I calculate the number of responses say of M1 in the "morning" timeframe of 06:00:00 - 09:59:59 (all data has hour, minute, second).

Thanks very much in advance. Hopefully this was clear enough. Grad work is a pain. :)


Morning (06:00 - 9:59)
Mid-Day (10:00 - 13:59
Afternoon (14:00 - 18:59)
Evening (19:00 - 23:59)
Overnight (00:00 - 05:59)

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

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top