Count a Column only when the Row is Not Marke

[Closed]
Report
-
Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
-
I have been trying to come up with the forumla that will do this last calculation to finish the spreadsheet but it is stumping me. I have 6 Columns (A, B, C, D, E, F) and three rows (1, 2, 3). I need a formula that will count A1 in the total marked for column A only if F1 is not also marked. I need this to work for rows 1-3. For example if A1 is marked and F1 is not marked the total for column A should be 1. If A1 and F1 are both marked than the value for column A should be zero. Thanks for the help. This one is killing me.

Jason

1 reply

Posts
190
Registration date
Sunday April 12, 2009
Status
Member
Last seen
February 16, 2010
155
Suppose for marking purpose we are using character 1 then use below formula ....

=SUMPRODUCT(--(A1:A3=1),--(F1:F3<>1))

Array formula will also work but don't forget to press Control + Shift + enter === {} brackets will appear after pressing Control + Shift + enter

=SUM(IF(A1:A3=1,1,0)*(IF(F1:F3<>1,1,0)))

Don't hesitate to contact me again if you have any query