Zero or One using Sum-index-match formula

oddy73 · Mar 15th 2015

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.

**PCI** · Mar 15th 2015

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)

oddy73 · Mar 19th 2015

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)

**PCI** · Mar 19th 2015

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

Thank you for the feed back

Zero or One using Sum-index-match formula

Participate now!

Share