Counting in two columns

Frogggg

Board Regular
Joined
Sep 10, 2007
Messages
67
To simplify my problem

I have two columns grade and # of days books are overdue.
I need to count how many times each grade has overdue books in a certain time frame

eg
Grade 1 15 days
Grade 1 15 days
Grade 2 65 days
Grade 2 57 days
Grade 2 5 days

How many times has grade 1 had books overdue for a) less than 1 week b) between 1 week to 1 month c) between 1 month and two months and d) more than 2 months.

this is the formula i have so far, it works for a) but obviously not b) c) or d)

=SUMPRODUCT(N($B$2:$B$1000="Grade 1"),N($C$2:$C$1000=7))
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
511
Office Version
365
Platform
Windows
Hi

I would suggest a pivot table.
<table valign="middle" colspan="4" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="40,5pt"><col width="45,75pt"><col width="99pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="4" align="middle">Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="left">Grade</td><td align="left"># of days</td><td align="left">overdue</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="left">Grade 1</td><td align="right">15</td><td align="left">1 week - 1 month</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="left">Grade 1</td><td align="right">15</td><td align="left">1 week - 1 month</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="left">Grade 2</td><td align="right">65</td><td align="left">more than 2 months</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="left">Grade 2</td><td align="right">57</td><td align="left">1 month - 2 months</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="left">Grade 2</td><td align="right">5</td><td align="left">less than 1 week</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Zelle</td><td>Formel</td></tr><tr><td>C2</td><td>=IF(D2,"less than 1 week",IF(E2,"1 week - 1 month",IF(F2,"1 month - 2 months","more than 2 months")))</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>

<table valign="middle" colspan="6" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="99pt"><col width="81,75pt"><col width="85,5pt"><col width="95,25pt"><col width="99pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="6" align="middle">Arbeitsblatt mit dem Namen 'Tabelle3'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">B</td><td align="middle">C</td><td align="middle">D</td><td align="middle">E</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">2</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">3</td><td align="left">Number of # of days</td><td align="left">overdue</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">4</td><td align="left">Grade</td><td align="left">less than 1 week</td><td align="left">1 week - 1 month</td><td align="left">1 month - 2 months</td><td align="left">more than 2 months</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">5</td><td align="left">Grade 1</td><td align="right">
</td><td align="right">2</td><td align="right">
</td><td align="right">
</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">6</td><td align="left">Grade 2</td><td align="right">1</td><td align="right">
</td><td align="right">1</td><td align="right">1</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></tbody></table>
 

Frogggg

Board Regular
Joined
Sep 10, 2007
Messages
67
thanks, but can it be done without a pivot table as I have never used them before and don't have the first clue how to
 

Mr_Roscoe

Board Regular
Joined
Mar 28, 2006
Messages
200
Frogggg - I would encourage you to learn Pivot Tables as they take away a whole lot of work. What I am about to give you is much easier via a Pivot Table. Besides, it is just a way of displaying data - so you cant break anything - so give it a go!

To resolve this problem your formula (using SHIFT-DEL's cell/row ref's) needs to be :

D2=IF(C2<7,"less than 1 week",IF(C2<30,"1 week - 1 month",IF(C2<61,"1 month - 2 months","more than 2 months")))

Copy the formula down from D3 to D6. Now you have a category for each row. Now the formula E2=B2&D2 and copy the formula again from E3 to E6.

Use SHIFT-DEL's Table 3 layout in cells F3 to J6

Type "Grade 1" into cell F5 and "Grade 2" into cell F6 (without "'s).
Type "less than 1 week" into G4, "1 week - 1 month" into H4, "1 month - 2 months" into I4 and "more than 2 months" into J4.

So you now have row headers and cloumn headers. Here's the formula to count data:

G5=COUNTIF($E:$E,$F5&G$4)

Autofill this formula from G5 across to cell J5 then autofill down to the next row and you should have the same info as a pivot table.

trust this helps, Ian R.
 

Frogggg

Board Regular
Joined
Sep 10, 2007
Messages
67
thank you that did work. I appreciate all the help everyone gives me in this forum. It is a lifesaver!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,974
Messages
5,508,511
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top