Neeeed Help!!

[luckyt22]

Ok so here is my problem. I have a report i need to do every month
based off of data. I have an excel sheet for the whole year of 2005 so
far. Each entry consists of reports submitted to our department and the
date it was submitted. It also says if there was a discrepancy with it
or not. If it doesn't, it says N/A. If it does, it specifies from a
dropdown menu. So for this upcoming month i need to do may 2005. I
want to count the number of reports that came in between certain dates
and that had a discrepancy. There is one column that has received
date, and another one with the discrepancies. I was able to do a
function for the TOTAL NUMBER of reports in a time frame. But then i
want to know ...of those reports taken, how many had discrepancies.
This goes further, but ill start with this question for now. the
answer may help me with the others.

THANKS!

 

[Biff]

Hi!

Try this.

Dates are in column A, A2:A20
Discrepancies are in column B, B2:B20

If you want to specify a date range, enter the start date and end date in
cells:

C1 = start date
D1 = end date

You didn't say whether or not any cells in the discrepancies column can or
will be empty so I added that condition to the formula:

=SUMPRODUCT(--(A2:A20>=C1),--(A2:A20<=D1),--(B2:B20<>""),--(B2:B20<>"N/A"))

Biff

 

[luckyt22]

this is what i inputed....and i got a #num! error...

=SUMPRODUCT(--('T&E Log'!E:E>=Holidays!K2),--('T&E
Log'!F:F<=Holidays!L2),--('T&E Log'!L:L<>"N/A"))

since i dont need to count blank cells and only the ones where N/A are
in...i didn't use the 3rd part of the function....let me know what i
should do...and i appreciate your help!!

Thanks

 

[luckyt22]

this is the way i did it....still get that error,

=SUMPRODUCT(--('T&E Log'!F:F>=Holidays!K2),--('T&E
Log'!F:F<=Holidays!L2),--('T&E Log'!L:L<>"N/A"))

 

[luckyt22]

when i traced the error...there are arrows pointing to the start dat
and end date...i formatted all the cells in regards to the date as dat
in the same format....no idea what the problem i

 

[luckyt22]

GOT IT! awesome thanks so much...i might need some more help so kee
checking! ha

thanks

 

[Biff]

Hi!

One problem is that you're using entire columns in the formula. Sumproduct
will not accept entire columns as range arguments. Use smaller ranges. Or,
you could use the entire column less one row: A1:A65535, A2:A65536

Make sure all the dates are really dates and not just a text strings.

Biff

 

[Harlan Grove]

luckyt22 wrote...

this is the way i did it....still get that error,

=SUMPRODUCT(--('T&E Log'!F:F>=Holidays!K2),
--('T&E Log'!F:F<=Holidays!L2),--('T&E Log'!L:L<>"N/A"))

You can't use entire column references in SUMPRODUCT (or more generally
in expressions that will be evaluated as arrays rather than ranges).
You'll need to restrict yourself to only 65,535 rows rather than 65,536
rows.

 

[luckyt22]

ok i got that to work..now for my next functions...i think these coul
be based on what you've already told me....so this is it...

I need to count the number of reports that were processed betwee
certain dates within a certain cycle date....i have 5 ranges...(0 days
1-2 days,3-5 days, 6-10, 10+) so i need a total of 5 functions....
have a column in the data that is cycle date...those have the number o
days....so i need to count the ones that say 0, or 1-2, and so on BU
between certain dates. I figure its the same as the last one except
have to change the "n/a" part. not sure how though!

thanks

 

[luckyt22]

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&
Log'!G3:G20000<=B4),--('T&E Log'!G3:H20000="0"))

this is for the range of just reports that had cycle dates of 0 days.

i'm getting a #value! error...

Thanks again

 

[anilsolipuram]

it should be

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&
Log'!G3:G20000<=B4),--('T&E Log'!H3:H20000="0")

 

[Biff]

Hi!

What are the *EXACT* values you use for these cycle ranges?

If a cycle is 1-2 days, does the cell literally have "1-2 days" entered into
it?

so i need a total of 5 functions

Why do you need 5 functions? You only need to enter one "formula" (and copy
it) that refers to 1 of 5 cells.

Need to know "If a cycle is 1-2 days, does the cell literally have "1-2
days" entered into it?"

Biff

 

[luckyt22]

sorry for not specifying...
the cycle date only has one number in it....so its either 1 ...or 2....

 

[luckyt22]

also...you said i can just refer it to other cells instead of using 5
different functions...thats fine...but what should i put in the cells
being referenced...like for ones with a range of cycle dates.... for
example the 6-10 range....if i have "6-10" in a cell and reference
it....will the function count cycle dates with 6,7,8,9,10 in it? i
tried to figure it out and came up with this for the 0 days one...

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&E
Log'!G3:G20000<=B4),--('T&E Log'!H3:H20000="0"))

but i get a #value! error....now for the 1-2 day cycle can i just do
this?

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&E
Log'!G3:G20000<=B4),--('T&E Log'!H3:H20000="A1")) cell a1 would read
like this exactly 1-2

let me know!

 

[luckyt22]

how do i hide the negative sign when a cell calculates a negativ
number? I don't want to use ABS though...because if i do another cel
will get screwed up...so i just want a negative number to appear w/
the negative sign in front of i

 

[luckyt22]

found out i wasnt suppose to have quotes so i took em out and it work
for some but not al

 

[Biff]

Hi!

Put the cycle range values in a series of cells somewhere, say, J1:J11
where:

J1 = 0
J2 = 1
J3 = 2
J4 = 3
J5 = 4
J6 = 5
J7 = 6
J8 = 7
J9 = 8
J10 = 9
J11 = 10


To count all that are 0:

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&ELog'!G3:G20000<=B4),--('T&E
Log'!H3:H20000=J1))

To count all that are "6 - 10 days":

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&ELog'!G3:G20000<=B4),--('T&E
Log'!H3:H20000>=J7)
--('T&E Log'!H3:H20000<=J9)

To count all that are "10+":

=SUMPRODUCT(--('T&E Log'!G3:G20000>=A4),--('T&ELog'!G3:G20000<=B4),--('T&E
Log'!H3:H20000>J11)

Biff

 

[luckyt22]

this is what i've been trying and i keep getting a #value! error. N
idea why...everything is logical and makes sense....i did it just how
you've said to

any advice

 

[luckyt22]

=SUMPRODUCT(--('T&
Log'!G3:G999>=A4),--('T&ELog'!G3:G999<=B4),--('T&ELog'!H3:H999>A42))

this is what i did for the 0 days and i'm getting a #ref! error no

 

[luckyt22]

last question...

i'm nearing the end of this worksheet dont worry!!

i need to still know how to do the 0 days, etc. function
and i still need to know the negative sign thing

and finally,
i have a dollar amount column for each entry....i want to add up dollar
amounts only for entries taht fall between the infamous two
dates....same concept as usual just adding the amounts up