# Need HELP to create a Simple Report

#### rajtak

##### Board Regular
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.

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

use some thing like :-

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

in the formulae. Thks

Kaps

#### RoryA

##### MrExcel MVP, Moderator
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

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

Replies
10
Views
146
Replies
2
Views
64
Replies
6
Views
174
Replies
1
Views
83
Replies
2
Views
39

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.

### Which adblocker are you using?

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

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