Counting in two columns

[Frogggg]

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))

 

[shift-del]

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]

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]

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]

thank you that did work. I appreciate all the help everyone gives me in this forum. It is a lifesaver!