Formula for Counting Case Hours of Certain Months

[EyzOnFire]

I can't seem to find the right formula to add hours based on the criteria that it is Jan or Feb, etc...

i've tried Sumif, but can't get the Criteria right.

Please help!
Thanks,
Eyz

[Aladin Akyurek]

On 2002-05-15 09:05, EyzOnFire wrote:
I can't seem to find the right formula to add hours based on the criteria that it is Jan or Feb, etc...

i've tried Sumif, but can't get the Criteria right.

Please help!
Thanks,
Eyz

In which columns are your dates and hours to sum? Tell also at which they start.

[Asala42]

Hey there,

Are you pretty much just trying to do a sum by month? Pivot tables can be real handy for this sort of thing. But a formula approach would be:

=SUMIF(A2:A100,"Jan",B2:B100)

...if you had the word "JAN" written out in ColA. Another one would be:


=SUMPRODUCT((MONTH(A2:A100)=1)*(B2:B100))

..if you have real dates in ColA and the sum data in ColB.

Hope that helps a bit,
Adam

[EyzOnFire]

Thank you both...

1 column has hours totaled from another page
1 column has dates in date format (01/01/99 to current date)
on another page i'm running stats

Total hours for January, etc

i used
=SUMIF('Cases Summary'!E2:E36,MONTH(>="01/01/02"<="01/31/02"),'Cases Summary'!N2:N36)

i've changed the Month criteria a million times and can't find a proper statement!...

do i use the same method you suggested to get my totals?

would a pivot table be a better solution for the long run? (this data will grow to millions of lines of data, right now it is just in the hundreds)

Thanks again,
eyz

[Aladin Akyurek]

On 2002-05-15 09:43, EyzOnFire wrote:
Thank you both...

1 column has hours totaled from another page
1 column has dates in date format (01/01/99 to current date)
on another page i'm running stats

Total hours for January, etc

i used
=SUMIF('Cases Summary'!E2:E36,MONTH(>="01/01/02"<="01/31/02"),'Cases Summary'!N2:N36)

i've changed the Month criteria a million times and can't find a proper statement!...

do i use the same method you suggested to get my totals?

would a pivot table be a better solution for the long run? (this data will grow to millions of lines of data, right now it is just in the hundreds)

Thanks again,
eyz

Insert a new column next to E in Cases Summary if current column F is not free.

In F2 enter:

=MONTH(E2)

Give a double click on the fill handle of F2.

Create first in a column range, say, in A from A2 on the month numbers in the sheet where you tried your original SUMIF formula.

In B2 enter:

=SUMIF('Cases Summary'!$F$2:$F$36,A2,'Cases Summary'!$N$2:$N$36)

Custom format the formula cell as [h]:mm then copy it down for 12 rows.

Millions... I don't think that's possible to pour it to PivotTables without any aggregation first. A formula-based system to process such an amount of data would require aggregation too. You should probably consider using a database system like Access.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-15 11:42 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-15 11:43 ]

[Mark W.]

...would a pivot table be a better solution for the long run? (this data will grow to millions of lines of data, right now it is just in the hundreds)

Thanks again,
eyz

A PivotTable would be ideal. You can source a PivotTable from external data. You can also Group your dates by month.

[EyzOnFire]

USING
on 1 page in column Q: =MONTH(E2)(got numbers for the month...looks accurate)I copied down the column(double clicking didn't do anything when i double clicked on column head)

on second step
you asked to go back to page of original formula and go to column (where the totals are supposed to be)... i'm not sure what you wanted me to do here

on third step
in Feb column (i'm assuming your B2) i entered your formula:
=SUMIF('Cases Summary'!Q2:Q36,A2,'Cases Summary'!N2:N36)

i formated the cell as directed and copied over

Result:
well...i got a number in Feb column but it was not the accurate total hours for the column in the other page... the rest of the month columns were zeros.

and the system does not like the MONTH(>="01/01/02"<="01/31/02) formula...


eyz

[Aladin Akyurek]

May I suggest re-reading my post: I edited a bit.


on 1 page in column Q: =MONTH(E2)(got numbers for the month...looks accurate)I copied down the column(double clicking didn't do anything when i double clicked on column head)

Fill handle is the little black square in the lower right of the cell. I said column F. If you've done in Q, adjust the formula given in my original post.

on second step
you asked to go back to page of original formula and go to column (where the totals are supposed to be)... i'm not sure what you wanted me to do here

Substitute the new formula I suggested in lieu of your own version.

on third step
in Feb column (i'm assuming your B2) i entered your formula:
=SUMIF('Cases Summary'!Q2:Q36,A2,'Cases Summary'!N2:N36)

We need to lock the ranges before we can copy down (I had forgotten that in my unedited post). Change this to:

=SUMIF('Cases Summary'!$Q$2:$Q$36,A2,'Cases Summary'!$N$2:$N$36)

Now it will work.


and the system does not like the MONTH(>="01/01/02"<="01/31/02) formula...

Of course not. That needs a different syntax. But now you don't need that because you have a month column built on your date column.

[EyzOnFire]

Aladin...
thank you so much for your help...and i don't want to seem dimwitted but...

this is still not working

the totals page is not working

i have heading down the left...and headings across the top

columsn A and B appear like this:
Totals
Total Number Case Hours 99.55
Avg Hours per Case 2.84
Total Number Cases 35
Avg Cases per month
Open Cases
Closed Cases
Total Cases Accepted 27
Total Cases Denied 6
then in the next 12 columns (C-N) i want monthly breakdowns of the above totals

the function in C3 should be?

i have the function in D3 done and copied...but it refers to C3...so... what is the first function?

eyz


[ This Message was edited by: EyzOnFire on 2002-05-15 12:41 ]

[ This Message was edited by: EyzOnFire on 2002-05-15 12:44 ]

[Aladin Akyurek]

On 2002-05-15 12:25, EyzOnFire wrote:
Aladin...
thank you so much for your help...and i don't want to seem dimwitted but...

this is still not working

the totals page is not working

i have heading down the left...and headings across the top (Break down for Jan(C3), Feb(D3), etc...)

it appears like this:
Total Jan Feb
Total Number Case Hours 99.55
Avg Hours per Case 2.84
Total Number Cases 35
Avg Cases per month
Open Cases
Closed Cases
Total Cases Accepted 27
Total Cases Denied 6
the function in C3 should be?

i have the function in D3 done and copied...but it refers to C3...so... what is the first function?

eyz

Here is an example that illustrates your problem as I understood it:

I asked you to create an additional column to compute months from dates in the sheet where you have hours that you want to total per month in a different sheet:

Microsoft Excel - Book11_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F4		=
*	A	B	C	D
1	date	month	hours	*
2	1/2/2002	:alert('=MONTH(A2)')>1	12:45	*
3	1/5/2002	:alert('=MONTH(A3)')>1	10:30	*
4	2/7/2002	:alert('=MONTH(A4)')>2	12:24	*
5	3/8/2002	:alert('=MONTH(A5)')>3	23:56	*
6	3/13/2002	:alert('=MONTH(A6)')>3	12:08	*
7	*	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


The following figure shows how to use SUMIF to compute the desired totals in a different sheet:

Microsoft Excel - Book11_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1		=	Month
*	A	B	C	D
1	Month	Total	*	*
2	1	:alert('=SUMIF(Sheet1!$B$2:$B$6,A2,Sheet1!$C$2:$C$6)')>23:15	*	*
3	2	:alert('=SUMIF(Sheet1!$B$2:$B$6,A3,Sheet1!$C$2:$C$6)')>12:24	*	*
4	3	:alert('=SUMIF(Sheet1!$B$2:$B$6,A4,Sheet1!$C$2:$C$6)')>36:04	*	*
5	4	:alert('=SUMIF(Sheet1!$B$2:$B$6,A5,Sheet1!$C$2:$C$6)')>0:00	*	*
6	5	:alert('=SUMIF(Sheet1!$B$2:$B$6,A6,Sheet1!$C$2:$C$6)')>0:00	*	*
7	6	:alert('=SUMIF(Sheet1!$B$2:$B$6,A7,Sheet1!$C$2:$C$6)')>0:00	*	*
8	7	:alert('=SUMIF(Sheet1!$B$2:$B$6,A8,Sheet1!$C$2:$C$6)')>0:00	*	*
9	8	:alert('=SUMIF(Sheet1!$B$2:$B$6,A9,Sheet1!$C$2:$C$6)')>0:00	*	*
10	9	:alert('=SUMIF(Sheet1!$B$2:$B$6,A10,Sheet1!$C$2:$C$6)')>0:00	*	*
11	10	:alert('=SUMIF(Sheet1!$B$2:$B$6,A11,Sheet1!$C$2:$C$6)')>0:00	*	*
12	11	:alert('=SUMIF(Sheet1!$B$2:$B$6,A12,Sheet1!$C$2:$C$6)')>0:00	*	*
13	12	:alert('=SUMIF(Sheet1!$B$2:$B$6,A13,Sheet1!$C$2:$C$6)')>0:00	*	*
Sheet2

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo






[ This Message was edited by: Aladin Akyurek on 2002-05-15 12:45 ]