Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gunjani
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gunjani
 
Posts: n/a
Default Countif, Match or Sumif Formula

Why the "--" before ISNUMBER

Regards

Gunjani

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Chip Pearson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif Formula /Sort Bug??? orcfodder Excel Discussion (Misc queries) 2 January 12th 06 11:04 AM
CountIf formula results in the formula itself being displayed. NewKid Excel Worksheet Functions 9 December 22nd 05 12:10 AM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Which formula to use? countif, sumif, sumproduct zubee Excel Discussion (Misc queries) 3 September 2nd 05 08:16 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"