Count Function 2 Columns

[Jeze77]

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

[Jeze77]

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

[Toppers]

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

[Jeze77]

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

[Toppers]

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

[Toppers]

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

[Jeze77]

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

[Roni]

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]

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