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!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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" wrote in
message ...

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!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile:
http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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

thanks!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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

"luckyt22" wrote in
message ...

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile:
http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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.

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

I need to count the number of reports that were processed between
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....i
have a column in the data that is cycle date...those have the number of
days....so i need to count the ones that say 0, or 1-2, and so on BUT
between certain dates. I figure its the same as the last one except i
have to change the "n/a" part. not sure how though!

thanks!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

=SUMPRODUCT(--('T&E Log'!G3:G20000=A4),--('T&E
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!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

it should be

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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" wrote in
message ...

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

I need to count the number of reports that were processed between
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....i
have a column in the data that is cycle date...those have the number of
days....so i need to count the ones that say 0, or 1-2, and so on BUT
between certain dates. I figure its the same as the last one except i
have to change the "n/a" part. not sure how though!

thanks!


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile:
http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

Hi!

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

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:H20000J11)

Biff

"luckyt22" wrote in
message ...

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
------------------------------------------------------------------------
luckyt22's Profile:
http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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

any advice?


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

=SUMPRODUCT(--('T&E
Log'!G3:G999=A4),--('T&ELog'!G3:G999<=B4),--('T&ELog'!H3:H999A42))

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

Can you send me your file? If you can and want to do that post an email
address and I'll contact you.

I'll fix it!

I'm getting confused!

Biff

"luckyt22" wrote in
message ...

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


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile:
http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856

its really weird because i have the formula copieed from my other cells
since it is the exact same way for most of these....and as soon as i
change the g in the last condition to H...which is the cycle date i get
a #value! error

dont understand


--
luckyt22
------------------------------------------------------------------------
luckyt22's Profile: http://www.excelforum.com/member.php...o&userid=24270
View this thread: http://www.excelforum.com/showthread...hreadid=378856