# Zero or One using Sum-index-match formula

• Hey guys,

I have this baseline and headcount parts in my sheet.
Baseline is for assigning the availability of a person per month and headcount should represent just a ONE or a ZERO, whether this person is assigned something (>0.1) or nothing (0) in the baseline.

I experimented with the sum-index-match formula in the headcount part, but was not successful to get the sum of my criteria and give as a result only a ONE or ZERO.

Code
``=SUM(INDEX(Q3:AC7,,MATCH(A3,P3:P7,0)))``

Please have a look in the attachment to understand what I mean…
Thanks.

## Files

• Re: Zero or One using Sum-index-match formula

Perhaps
B3=(SUMPRODUCT((\$P\$3:\$P\$6=\$A3)*(\$Q\$1:\$AC\$1=B\$1)*(\$Q\$3:\$AC\$6>0.1))>=1)*1
Copy and paste
Care about the date: Is used real date with month format, to avoid confusion when there is twice the same vale (e.g; Jan in you sample)

## Files

Triumph without peril brings no glory: Just try

• Re: Zero or One using Sum-index-match formula [Solved]

I am sorry for not responding quickly, but your solution was perfect! I only changed 0.1 to 0 (this was my mistake)

• Re: Zero or One using Sum-index-match formula

Thank you for the feed back

Triumph without peril brings no glory: Just try

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!