I've tried this one
=SUMPRODUCT(--('SCHEDULE WEEK ONE'!$Q$5:$Q$1300="")*('SCHEDULE WEEK ONE'!$V$5:$V$1300=D8)*('SCHEDULE WEEK ONE'!$U$5:$U$1300=B8))
but still get a zero.
Hello Everyone,
I have a formula that I need to use in two different ways.
The first it needs to ignore the blank cells in a range, and sum two other ranges that meet a single text string criteria in each range. But one of the text strings is a combination of text with a dash and then a letter and a number I just need the first four characters. I tried the formula in a few different ways with no luck usually getting a "0".
=SUMPRODUCT('SCHEDULE WEEK ONE'!$Q$5:$Q$1300="")*('SCHEDULE WEEK ONE'!$V$5:$V$1300=D8)*('SCHEDULE WEEK ONE'!$U$5:$U$1300=B8)
D8 is the text string that, four or more letter and then has a dash followed by a letter and number. The formula needs to ignore the "" in the range Q5:Q1300 then check the sum the other ranges.
Thank you for your help
this thread is not cross posted elsewhere.
MZING81
I've tried this one
=SUMPRODUCT(--('SCHEDULE WEEK ONE'!$Q$5:$Q$1300="")*('SCHEDULE WEEK ONE'!$V$5:$V$1300=D8)*('SCHEDULE WEEK ONE'!$U$5:$U$1300=B8))
but still get a zero.
AND TRIED THIS ONE
=SUMPRODUCT(--('SCHEDULE WEEK ONE'!$Q$5:$Q$1300="")--('SCHEDULE WEEK ONE'!$V$5:$V$1300=D8)*('SCHEDULE WEEK ONE'!$U$5:$U$1300=B8))
WHICH RETURNS, 508, BUT IT'S STILL OFF SHOUDL BE ABOUT 260ish.
Your formula appears to be counting only the occasions when there is a blank in the "Q" range, not excluding them. Try modifying like this:
Your description of the "first four Characters" is a little confusing, can you post a sample workbook to help clarify?Code:SUMPRODUCT(--('SCHEDULE WEEK ONE'!$Q$5:$Q$1300<>""),--('SCHEDULE WEEK ONE'!$V$5:$V$1300=D8),--('SCHEDULE WEEK ONE'!$U$5:$U$1300=B8))
Last edited by CheshireCat; 2012-09-13 at 06:34 PM.
Is this what you want
=SUMPRODUCT(--('SCHEDULE WEEK ONE'!$Q$5:$Q$1204<>""),
--('SCHEDULE WEEK ONE'!$V$5:$V$1204=D8),
--('SCHEDULE WEEK ONE'!$U$5:$U$1204=LEFT(B8,FIND("-",B8)-1)))
Why don't you post an example workbook.
Bookmarks