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!
