software

Your help greatly appreciated (first posting to a forum)
Scenario
One workbook; two worksheets. The second worksheet contains, across multiple columns, the results of formulas in multiple rows (corresponding to rows in worksheet one – ‘shifts’). The formula (referenced) is =IF(AND(I$1gt;=shifts!$J3,I$1lt;shifts!$L3),quot;1quot;,quot;0quot;). This places either the value ‘0’ or the value’1’ in a referenced cells. The column headings are from 06:00 hrs to 24:00 in 15 minute increments.

The sheet contains columns to the left with codes (either ‘F’; ‘1’; ‘2’ and so on). At the bottom of each list of 15 minute columns containing the formulas (which shown the results – either ‘0’ or ‘1’) I want to add up using =SUMIF(Mon!A2:A10,quot;Fquot;,Mon!I2:I10), which refers in this case to code ‘F’, all the ‘1’s’ where ‘F’ is the code.

This works satisfactorily in a test sheet where ‘0’ and ‘1’ are entered manually but not when these values are the results of the IF/AND formula.

I have prepared a cut down Workbook with some actuals in it if required and also have typed illustration to put here if required (but thought might be too much - this being new to me and not wanting to upset).

Been looking on many sites for solution over past days. 22:00 signing off until tomorrow after work. In anticipation and gratitude.

Drop the double quotes from your formula

quot;1quot; is text whereas 1 is a number

Does this help?

proverbs Wrote:
gt; The formula
gt; (referenced) is =IF(AND(I$1gt;=shifts!$J3,I$1lt;shifts!$L3),quot;1quot;,quot;0quot;). This
gt; places either the value ‘0’ or the value’1’ in a referenced cells.
gt;
gt; This works satisfactorily in a test sheet where ‘0’ and ‘1’ are
gt; entered
gt; manually but not when these values are the results of the IF/AND
gt; formula.
gt;--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=533978Oh thank you John, yes it does. One for the records!

Phill (alias proverbs)
with apology to administrator for not yet understand the 'snip'Originally Posted by John JamesDrop the double quotes from your formula

quot;1quot; is text whereas 1 is a number

Does this help?

proverbs Wrote:The formula(referenced) is =IF(AND(I$1=shifts!$J3,I$1shifts!$L3),quot;1quot;,quot;0quot;). Thisplaces either the value ‘0’ or the value’1’ in a referenced cells.
This works satisfactorily in a test sheet where ‘0’ and ‘1’ areenteredmanually but not when these values are the results of the IF/ANDformula.
--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=533978