Different data in multiple cells

[bluewaternavy]

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
M9	5-U
M8 <colgroup><col></colgroup><tbody> </tbody>	8-U
M15	15-R
M5	5-U
M9	5-U
M4	4-U
M9	5-U
M715	9-U
M205	5-U
M215	5-U

<tbody>
</tbody>

Thanks for the help in advance.

---

---

 

[circledchicken]

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]

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]

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]

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]

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]

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]

Thanks. That all really helps! Have a great weekend

 

[circledchicken]

You're welcome - thanks for the feedback, and have a great weekend too.

 

[bluewaternavy]

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>