Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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!!!!

Count Function 2 Columns
 

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