Countif

K

Ken

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
D

Dave Peterson

Your original formula (a bit easier to read):

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)
-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")
-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")
-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")
-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")
-COUNTIF('DATA (RAW)'!AR:AR,"CA")
-COUNTIF('DATA (RAW)'!AR:AR,"US")

Maybe you could use this kind of thing (_I think_):

=SUMPRODUCT(--('Data (Raw)'!BH1:BH11>DashBoard!AD3),
--('Data (Raw)'!N1:N11<>"SUPPORT REQUEST")
--('Data (Raw)'!N1:N11<>"WORK ORDER"),
--('Data (Raw)'!AQ1:AQ11<>"EMEA"),
--('Data (Raw)'!AQ1:AQ11<>"CALA"),
--('Data (Raw)'!AR1:AR11<>"CA"),
--('Data (Raw)'!AR1:AR11<>"US"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========

This formula wants the cells in each row of BH1:BH11 to be bigger than the date
in Dashboard!AD3

And at the same time N1:n11 different from "support request"
and at the same time N1:n11 different from "work order"

and at the same time aq1:aq11 different from "emea"
and ....


I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
B

bpeltzer

1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))
 
J

jlclyde

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date  [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!

The second part shoud read =COUNTIF('DATA (RAW)'!BH:BH,DASHBOARD!AD3)
It assumes that you are using equal you only have to quote it out if
it is another inequality symbol.
 
K

Ken

The solution you gave for Question 2 worked perfectly. Thanks!

I'm trying to digest the =sumproduct formula now. I'll test and let you know
if the solution to Question 1 works .

Best Regards,

bpeltzer said:
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


Ken said:
I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
K

Ken

Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken


bpeltzer said:
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


Ken said:
I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
K

Ken

Ok ... I tried the following and it worked

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3)*(--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")))

I'll try to add a few more exceptions to the end of this and see if it
works.

Regards,

bpeltzer said:
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


Ken said:
I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
D

Dave Peterson

Your post misplaced a comma and some ()'s.

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3),
--('DATA (RAW)'!N1:N2000<>"SUPPORT REQUEST"))


Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken

bpeltzer said:
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


Ken said:
I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
K

Ken

Actually this is what finally worked for me:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)*(--('DATA
(RAW)'!N2:N5000<>"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<>"WORK
ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"))*(--('DATA
(RAW)'!AQ2:AQ5000<>"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<>"NA"))) This is a
straight cut and paste from Excel.

I tried using the , but it kept correcting me and removing them.

Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA
(RAW)'!BH1:BH2000>DASHBOARD!AD3)) that gave me the boost I needed along with
the links you provided.

You and bpeltzer definitely saved the day. My report is now in a "Ready for
Test" state.

Best Regards,

Ken

Dave Peterson said:
Your post misplaced a comma and some ()'s.

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3),
--('DATA (RAW)'!N1:N2000<>"SUPPORT REQUEST"))


Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken

bpeltzer said:
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


:

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
D

Dave Peterson

You don't need both the multiply and --.

Either:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3),
--('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST"),
--('DATA (RAW)'!N2:N5000<>"WORK ORDER"),
--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"),
--('DATA (RAW)'!AQ2:AQ5000<>"CALA"),
--('DATA (RAW)'!AQ2:AQ5000<>"NA"))

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"NA"))

Ps. Sometimes, it makes the formula lots easier to read if you force a new line
when you're typing it into the formula bar. Just use alt-enter like when you
want to enter a label like:

Top
Bottom

I'll also use the spacebar to line things up.
Actually this is what finally worked for me:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)*(--('DATA
(RAW)'!N2:N5000<>"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<>"WORK
ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"))*(--('DATA
(RAW)'!AQ2:AQ5000<>"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<>"NA"))) This is a
straight cut and paste from Excel.

I tried using the , but it kept correcting me and removing them.

Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA
(RAW)'!BH1:BH2000>DASHBOARD!AD3)) that gave me the boost I needed along with
the links you provided.

You and bpeltzer definitely saved the day. My report is now in a "Ready for
Test" state.

Best Regards,

Ken

Dave Peterson said:
Your post misplaced a comma and some ()'s.

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3),
--('DATA (RAW)'!N1:N2000<>"SUPPORT REQUEST"))


Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken

:

1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


:

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
K

Ken

That works like a charm and it is much easier to read.

Thanks again for all your help!

Regards,

Ken

Dave Peterson said:
You don't need both the multiply and --.

Either:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3),
--('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST"),
--('DATA (RAW)'!N2:N5000<>"WORK ORDER"),
--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"),
--('DATA (RAW)'!AQ2:AQ5000<>"CALA"),
--('DATA (RAW)'!AQ2:AQ5000<>"NA"))

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"NA"))

Ps. Sometimes, it makes the formula lots easier to read if you force a new line
when you're typing it into the formula bar. Just use alt-enter like when you
want to enter a label like:

Top
Bottom

I'll also use the spacebar to line things up.
Actually this is what finally worked for me:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)*(--('DATA
(RAW)'!N2:N5000<>"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<>"WORK
ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"))*(--('DATA
(RAW)'!AQ2:AQ5000<>"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<>"NA"))) This is a
straight cut and paste from Excel.

I tried using the , but it kept correcting me and removing them.

Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA
(RAW)'!BH1:BH2000>DASHBOARD!AD3)) that gave me the boost I needed along with
the links you provided.

You and bpeltzer definitely saved the day. My report is now in a "Ready for
Test" state.

Best Regards,

Ken

Dave Peterson said:
Your post misplaced a comma and some ()'s.

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3),
--('DATA (RAW)'!N1:N2000<>"SUPPORT REQUEST"))



Ken wrote:

Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken

:

1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


:

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 
D

Dave Peterson

And easier to update, too!
That works like a charm and it is much easier to read.

Thanks again for all your help!

Regards,

Ken

Dave Peterson said:
You don't need both the multiply and --.

Either:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3),
--('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST"),
--('DATA (RAW)'!N2:N5000<>"WORK ORDER"),
--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"),
--('DATA (RAW)'!AQ2:AQ5000<>"CALA"),
--('DATA (RAW)'!AQ2:AQ5000<>"NA"))

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"NA"))

Ps. Sometimes, it makes the formula lots easier to read if you force a new line
when you're typing it into the formula bar. Just use alt-enter like when you
want to enter a label like:

Top
Bottom

I'll also use the spacebar to line things up.
Actually this is what finally worked for me:

=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)*(--('DATA
(RAW)'!N2:N5000<>"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<>"WORK
ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<>"EMEA"))*(--('DATA
(RAW)'!AQ2:AQ5000<>"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<>"NA"))) This is a
straight cut and paste from Excel.

I tried using the , but it kept correcting me and removing them.

Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA
(RAW)'!BH1:BH2000>DASHBOARD!AD3)) that gave me the boost I needed along with
the links you provided.

You and bpeltzer definitely saved the day. My report is now in a "Ready for
Test" state.

Best Regards,

Ken

:

Your post misplaced a comma and some ()'s.

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3),
--('DATA (RAW)'!N1:N2000<>"SUPPORT REQUEST"))



Ken wrote:

Hi bpeltzer,

With your help and Dave's help I a bit further ahead than I was this
morning. Here is where I'm stuck

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))

The above works and it returns a value of 683. The problem is the exceptions
part. For testing I tried the following:

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000>DASHBOARD!AD3))--('DATA
(RAW)'!N1:N2000<>"SUPPORT REQUEST")

This returned a value of 684 when it should have returned a value of 578.
Any ideas why the exception portion fails?

Ken

:

1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the
date criterion and winding up with too low a number. I think you're working
with criteria from different fields and probably want to consider a
sumproduct formula... something like
=sumproduct(--(bh1:bh2000>Dashboard!AD3),--(an1:an2000="ICMS Request"))

2nd - The date value in your worksheet may have a time component but be
formatted so that only the date shows. If so, then the equality test will
fail, as the worksheet has a fractional component you're missing in the
comparison. Instead you might have to test for a range:
=COUNTIF('DATA (RAW)'!BH:BH,">="&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!BH:BH,">="&(DASHBOARD!AD3 + 1))


:

I have two questions.

1st- Can someone tell me another way to write this statement? This is not
working for me and I don't understand why. This dashboard portion refers to
today's date [">"&DASHBOARD!AD3] and it works fine. The problem is I need to
remove or exclude some of the other data so I can filter per Region and only
show "ICMS Request" values.

=COUNTIF('DATA (RAW)'!BH:BH,">"&DASHBOARD!AD3)-COUNTIF('DATA
(RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK
ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA
(RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA
(RAW)'!AR:AR,"US")

2nd - Is there a way to count only the values that = today's date? The
following doesn't work:

=COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)

Appreciate the help ... this report is killing me!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top