In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

I have re-tested with various combinations of data and had no problems.
Formula is NOT entered with CTRL/SHIFT/ENTER which your posting showing curly
brackets {} might indicate you are doing.

As I said earlier, check data is formatted OK.

"Jeze77" wrote:

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

In your formula, there is comma missing between the two elements of the
SUMPRODUCT statement: this will give erroneous results. Look at my version.

HTH

"Toppers" wrote:

I have re-tested with various combinations of data and had no problems.
Formula is NOT entered with CTRL/SHIFT/ENTER which your posting showing curly
brackets {} might indicate you are doing.

As I said earlier, check data is formatted OK.

"Jeze77" wrote:

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

thank you!

"Toppers" wrote:

In your formula, there is comma missing between the two elements of the
SUMPRODUCT statement: this will give erroneous results. Look at my version.

HTH

"Toppers" wrote:

I have re-tested with various combinations of data and had no problems.
Formula is NOT entered with CTRL/SHIFT/ENTER which your posting showing curly
brackets {} might indicate you are doing.

As I said earlier, check data is formatted OK.

"Jeze77" wrote:

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

I am trying to do the SAME thing, but it is not working for me either.....
Here is what I have:

=SUMPRODUCT(--('Dr McNanley'!D:D="Vaginal"), --('Dr McNanley'!F:F="Grunert"))

I have also tried:
=SUMPRODUCT(--('Dr McNanley'!D:D="Vaginal"),--('Dr McNanley'!F:F="Grunert"))

Any help would be greatly appreciated!!!!

Thanks in advance!!!
Roni
"Jeze77" wrote:

thank you!

"Toppers" wrote:

In your formula, there is comma missing between the two elements of the
SUMPRODUCT statement: this will give erroneous results. Look at my version.

HTH

"Toppers" wrote:

I have re-tested with various combinations of data and had no problems.
Formula is NOT entered with CTRL/SHIFT/ENTER which your posting showing curly
brackets {} might indicate you are doing.

As I said earlier, check data is formatted OK.

"Jeze77" wrote:

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

You have a reply at your other post...

Don't use the whole column.

Roni wrote:

I am trying to do the SAME thing, but it is not working for me either.....
Here is what I have:

=SUMPRODUCT(--('Dr McNanley'!D:D="Vaginal"), --('Dr McNanley'!F:F="Grunert"))

I have also tried:
=SUMPRODUCT(--('Dr McNanley'!D:D="Vaginal"),--('Dr McNanley'!F:F="Grunert"))

Any help would be greatly appreciated!!!!

Thanks in advance!!!
Roni
"Jeze77" wrote:

thank you!

"Toppers" wrote:

In your formula, there is comma missing between the two elements of the
SUMPRODUCT statement: this will give erroneous results. Look at my version.

HTH

"Toppers" wrote:

I have re-tested with various combinations of data and had no problems.
Formula is NOT entered with CTRL/SHIFT/ENTER which your posting showing curly
brackets {} might indicate you are doing.

As I said earlier, check data is formatted OK.

"Jeze77" wrote:

it also counted for 50104 and 50105 then returned the same for "After Hours"
and that's not correct. When I copied and adjusted the formula to pull for
50104 and 50105, it returned the same incorrect # as a total.
Thanks

"Toppers" wrote:

Worked OK for me.

=SUMPRODUCT(--('November 05'!B1:B5="50101"),--( 'November
05'!K1:K5="Overnight"))



Check 50101 is text not numeric

"Jeze77" wrote:

Ok so {=SUMPRODUCT(--(B1:B5="50101")--(K1:K5="Overnight"))} works if i keep
it on the same sheet...but it won't work a different worksheet as
{=SUMPRODUCT(--('November 05'!B1:B5="50101")--('November
05'!K1:K5="Overnight"))}
WHY? What am i doing wrong?

"Jeze77" wrote:

In Column B I have up to 30 different center #'s (example 50101, 50202, 50303
and so on) In column K, I have either "Overnight" or "After Hours". I need a
formula on a seperate sheet (center #'s as headers, doesn't matter vertical
or horizontal) to return how many Overnights there are and how many After
Hours per month. A Pivot Table will work (too easy) but I'm dealing w/
non-excel users who keep breaking the pivot table and now all the data is
sorted on monthly worksheets rather than all data on one sheet.
Thanks in advance!!!!

--

Dave Peterson