SUM function and a condition involving a datevalue

[Alxis]

I'm trying to figure yet another formula here. I'm trying to do the following:

I want to sum up the numbers of column C, provided that column AA has a specific date value of "8-Oct-2010" and also has a value of "Y" in column B.

or said another way:

If one column (AA) from my 'Data' sheet has a date value in it (ex. 8-Oct-2010) and another column (B) has a value of "Y", then I want to sum up the numbers of column (C), which pertain to that specific date of 8-Oct-2010 and which also have a value of "Y" in column (B)

So...I tried the following formula:


=SUMPRODUCT((Data!$AA$2:$AA$100=$A$1)*(Data!$B$2:$B$100="Y")*(Data!$C$2:$C$100))

(Where A1 contains the date I want to match)


BUT...it keeps returning a 0 value!....Any help please!!

[peterjuhnke]

Please attach a dummy sheet explaining where you want the formula to be.
regards
Peter

[ConneXionLost]

Try this:

Please Login or Register  to view this content.

Cheers,

[DonkeyOte]

=SUMPRODUCT((Data!$AA$2:$AA$100=$A$1)*(Data!$B$2:$B$100="Y")*(Data!$C$2:$C$100))
(Where A1 contains the date I want to match)
...
BUT...it keeps returning a 0 value!....Any help please!!

Based on your original formula the issue could be either

a) dates in AA are not true dates (or conversely they are but A1 is not)

b) values in B contain leading or trailing spaces

To determine which... what do the below return ?

Please Login or Register  to view this content.

If the first (date) returns 0 and assuming ISNUMBER(A1) returns TRUE then try:

Please Login or Register  to view this content.

if the ISNUMBER on A1 returns FALSE then reverse the logic and move the +0 to the criteria

If B returns 0 then try:

Please Login or Register  to view this content.

If both COUNTIFs returned 0 then obviously combine the two.

In the above I have assumed values in C to be numeric and am thus not coercing (so as to reduce risk of coercion errors)

Note: the leading/trailing space is assumed - the issue could be related to CHAR(160) but we won't know until you report back re: above tests

[daddylonglegs]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

See crosspost here

[Alxis]

Based on your original formula the issue could be either



To determine which... what do the below return ?

Please Login or Register  to view this content.

I did the above and interestingly they both return a value of 1, it turns out the dates were not true dates. Thanks!

[DonkeyOte]

It would only return the value from C if both conditions held true on the same row
Though we know the criteria can be found (once each) we don't know if they appear in the same row (the SUMPRODUCT 0 implies not).

If you find the one instance of "Y" in Data!B2:B100 does the value in AA on the same row match that of your criteria date ?

No: then no records meet your requirements hence 0

Yes: check the value of C in the same row to see if it is 0
Given the initial approach coerced values in C (by means of *) I can't see "numbers stored as text" in Col C as being the issue.

[DonkeyOte]

You made a late edit to your prior post (ie after above reply) so in response to your edit...

What do the below return ?

Please Login or Register  to view this content.