Need HELP to create a Simple Report

[rajtak]

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.

 

[Kaps_mr2]

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]

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]

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]

use some thing like :-

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

in the formulae. Thks

Kaps

 

[RoryA]

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]

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]

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]

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]

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.