Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, Match or Sumif Formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, Match or Sumif Formula
=SUMPRODUCT(--(ISNUMBER(MATCH(R119:R126,B1:B2000,0))))-R117
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gunjani" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, Match or Sumif Formula
Why the "--" before ISNUMBER
Regards Gunjani |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, Match or Sumif Formula
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 "Gunjani" wrote in message oups.com... Why the "--" before ISNUMBER Regards Gunjani |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif, Match or Sumif Formula
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Gunjani" wrote in message oups.com... Why the "--" before ISNUMBER Regards Gunjani |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Which formula to use? countif, sumif, sumproduct | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions |