sumproduct formula

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

How do I get the following formula to work I want to count
the # of lines that have vendor in column L and a date
between the values referenced, and any cell that contains
the letter r in column f, please help.

SUMPRODUCT(((('Doc Receipts'!L1:L39997="Vendor")*('Doc
Receipts'!A1:A39997>=Breakdown!B5)*('Doc Receipts'!
A1:A39997<=Breakdown!D5)*('Doc Receipts'!
F1:F39997="*r*"))))
 
How do I get the following formula to work I want to count
the # of lines that have vendor in column L and a date
between the values referenced, and any cell that contains
the letter r in column f, please help.

SUMPRODUCT(((('Doc Receipts'!L1:L39997="Vendor")*('Doc
Receipts'!A1:A39997>=Breakdown!B5)*('Doc Receipts'!
A1:A39997<=Breakdown!D5)*('Doc Receipts'!
F1:F39997="*r*"))))
Click to expand...

You should mention how this doesn't work. Is it returning errors or numbers that
don't appear to be correct? Syntactically, there's nothing wrong with your
formula, so it's more likely than not theproblem lies in your data ranges.

If your formula were returning 0, then the middle two conditions (date range)
would be the prime suspects. Specifically, it'd seem your date range contains
dates as text rather than as date serial numbers. If so, copy an empty cell,
select 'Doc Receipts'!A1:A39997, and Edit > Paste-Special as Value *AND* Add.
Then give this range a date format if necessary. You can check if the date range
contains any text using

=IF(COUNT('Doc Receipts'!A1:A39997)<COUNTA('Doc Receipts'!A1:A39997),
"contains text","contains only numbers and blanks")
 
Back
Top