Need HELP to create a Simple Report

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74
Hi,

I've three columns excelsheet and want to create Per day report.

Date,Records,Status

I want to count per day records and status.

This will be self-explanatory when you check this screenshot. Please suggest me some functions/formulas.
Sample.jpg
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
the first part can be done via countif :-

COUNTIF(A1:A1000,E3)

the second and third via SUMPRODUCT :-

=SUMPRODUCT(--((A1:A1000)=DATE(2009,2,25)),--((B1:B1000)="Completed"))

Thanks

Kaps
 

Expiry

Well-known Member
Joined
Jun 20, 2007
Messages
865
The formula in F3 should be =countif(A:A,E3) copy this down column F

For column G and H, there are a few ways you could do this. The easiest way is to concatenate columns A & C (ie. join the two entries together) in column D. (you can make the font white so you can't see it, or hide the column).

In D2 type =A2&C2. Copy this down.

Then in G3 type =COUNT(D:D,$E3&G$2). Copy this to H3 and then copy both of this down as far as you need.
 

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74
Hi Kaps thanks for your suggestion, but i dont want to update the function once it is created, In your formula i'll have to keep changing the date instead i need a stable formula which i could easily drag all the way down.

=SUMPRODUCT(--((A1:A1000)=DATE(2009,2,25)),--((B1:B1000)="Completed"))
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538

ADVERTISEMENT

use some thing like :-

Date(year(e3),month(e3),day(e3))

in the formulae. Thks

Kaps
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I would use a pivot table for that. Put the date in the row field, the status in the column area, use count of records as the data and show totals for rows.
 

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74

ADVERTISEMENT

The formula in F3 should be =countif(A:A,E3) copy this down column F

For column G and H, there are a few ways you could do this. The easiest way is to concatenate columns A & C (ie. join the two entries together) in column D. (you can make the font white so you can't see it, or hide the column).

In D2 type =A2&C2. Copy this down.

Then in G3 type =COUNT(D:D,$E3&G$2). Copy this to H3 and then copy both of this down as far as you need.

Hi Expiry,
Thanks for your suggestion, but once i concatenate date and Status i.e. =A2&C2 the date gets changed to some numbers "39989" hence it is not working.
 

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74
I would use a pivot table for that. Put the date in the row field, the status in the column area, use count of records as the data and show totals for rows.

I never use Pivot table, Please enlighten me how do i create my report using this?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can find a good walkthrough for 2007 here. You need to put the Date field in the Row area, the Status field in the Column area and the Records field in the data area (it should default to count)
 

Expiry

Well-known Member
Joined
Jun 20, 2007
Messages
865
Hi Expiry,
Thanks for your suggestion, but once i concatenate date and Status i.e. =A2&C2 the date gets changed to some numbers "39989" hence it is not working.

But you're concatenating twice, changing the date in column A on one side and also the date in column E, as you have to match these up it should still work. It certainly works for me.
 

Forum statistics

Threads
1,136,433
Messages
5,675,829
Members
419,586
Latest member
RoteichA

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