Countif, Match or Sumif Formula

[Gunjani]

In Column B I have calender dates for the year Starting from 29/01/06
thru to 29/06/06

In Cell R117 I have a Value 1 ( which denotes Bank Holiday not worked)

In Cells R119 to 126 I have a list of Bank Holiday Days i.e .
02/01/06, 14/04/06, 17/04/06, 01/05/06, 29/05/06, 28/08/06, 25/12/06
and 26/12/06.

In Cell S117, I like to Create a Formula where If the Bank Holiday
dates (in Cells R117 to 126) fall in the range of Column B then deduct
the Count by the Value in Cell R117. (Bank Holiday worked in that
period)

In the above example the bank holidays 14/04, 17/04,01/05 and 29/05 are
in the Column B range, Hence those 4 days minus 1 (in Cell R117) would
give me a Value 3 in Cell S117.

Any Suggestions?

Regards

Gunjani

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(MATCH(R119:R126,B1:B2000,0))))-R117

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Gunjani]

Why the "--" before ISNUMBER

Regards

Gunjani

 

[Chip Pearson]

ISNUMBER returns a value of TRUE or FALSE. To convert these to
their numeric equivalent (1 or 0), the double negative sign is
used. It takes the negative of a negative, which coerces the TRUE
or FALSE value to numbers.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)