If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumproduct formula
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*")))) |
#2
|
|||
|
|||
sumproduct formula
"Sue" wrote...
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*")))) 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") -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
Thread Tools | |
Display Modes | |
|
|