Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default How to use RIGHT() in sumproduct

Hi,
Ive got the below formula to count the number of jobs (D:D) done by person
B24 in col. (I:I)
=SUMPRODUCT((INDIRECT("'"&C$23&"'!D8:D50")<"")*(I NDIRECT("'"&C$23&"'!I8:I50")=$B$24))
How do I account for all day jobs (want a value of 2) in Col. C which has
various time entries including "A/D" I wanted to look for the A/D using
RIGHT()but am completely stuck

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default How to use RIGHT() in sumproduct

Say in A1 thru B3 we have:

xxxA/D 1
xxxqwe 3
xxxA/D 5

the formula:

=SUMPRODUCT((RIGHT(A1:A10,3)="A/D")*(B1:B10)) will display 6
--
Gary''s Student - gsnu200839


"chrisk" wrote:

Hi,
Ive got the below formula to count the number of jobs (D:D) done by person
B24 in col. (I:I)
=SUMPRODUCT((INDIRECT("'"&C$23&"'!D8:D50")<"")*(I NDIRECT("'"&C$23&"'!I8:I50")=$B$24))
How do I account for all day jobs (want a value of 2) in Col. C which has
various time entries including "A/D" I wanted to look for the A/D using
RIGHT()but am completely stuck

Thanks

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
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Excel Worksheet Functions 4 August 14th 08 07:50 PM
Sumproduct Help PLEASE cdw Excel Worksheet Functions 1 November 12th 07 03:22 PM
SUMPRODUCT Ken[_2_] Excel Worksheet Functions 0 November 10th 07 04:56 PM
sumproduct? sumif(sumproduct)? David Excel Worksheet Functions 3 July 13th 07 07:06 PM
Can Sumproduct do this? dexman Excel Worksheet Functions 2 February 14th 06 03:01 AM


All times are GMT +1. The time now is 07:21 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"