Help with this formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

IN CELL B28 This formula returns the sum of a range of cells that contain
data that starts with a number, such as 1a15, 2a15, 3a15, 1b15, 2b15, etc.

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!A22:A43,1))))

Problem is that there is one type of data that starts with a number that I
do not want counted in the sum. It is "1desk".

How can this formula be changed so as to perform my request.
Thank You
Brian
 
One way:

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!A22:A43,1))),--('Patrol
Log'!A22:A43<>"1desk"))
 
Brian,

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!A22:A43,1))))-COUNTIF('Patrol
Log'!A22:A43,"1desk")

HTH,
Bernie
MS Excel MVP
 
How about?

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!A22:A43,1))))-COUNTIF('Patrol
Log'!A22:A43,"1desk")

Another way

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol
Log'!A22:A43,1))),--(ISERROR(FIND("1desk",'Patrol Log'!A22:A43))))

Best Regards,

Peo Sjoblom
 
=SUMPRODUCT(LEFT('Patrol Log'!A22:A44)*ISERROR(SEARCH
("desk",'Patrol Log'!A22:A44)))

HTH
Jason
Atlanta, GA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with this formi 1
COUNTIFS BETWEEN WORKSHEETS HELP 1
sumproduct formula 1
identify data in a cell that has a number as the second character 1
Complex conditional summing - array COUNT works, array SUM gives#VALUE 4
SUMIF with criteria "<>" & "=" 4
Excel Need Countifs Formula Help 0
Excel Sumproduct 0

Back
Top