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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. 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!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,815
Messages
5,655,460
Members
418,203
Latest member
ElizabethCorrin

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