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

=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!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:BH2000DASHBOARD!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:BH2000DASHBOARD!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" wrote:

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:bh2000Dashboard!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" wrote:

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!

--

Dave Peterson