Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default SUMPRODUCT with 3 criteria

I have 3 columns with the following data in:

RAG status e.g. red, amber, green
Types e.g. 1, 2, 3, 4
Project e.g. A1-AA1, A2-BC3, B1-HH1, B2-JK2, B3-JJ2

I want to be able to calculate all type 1s that are Green beginning with A
or B etc.

I have tried using the following formula:

=SUMPRODUCT(--($A$9:$A$85="Green"),--(LEFT($C$9:$C$85)="1"),--(LEFT($D$9:$D$85,7)="A*"))

Frustratingly, if I put the full name of the project in, the formula works,
but when I use wildcards it doesn't.



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
self-sizing adv.filter criteria range BorisS Excel Discussion (Misc queries) 3 September 24th 06 01:30 PM
SUMPRODUCT Criteria Via Cell Reference?? John V Excel Worksheet Functions 8 April 12th 06 07:55 PM
SumProduct with criteria list Sige Excel Worksheet Functions 17 February 21st 06 03:30 PM
Multiple Criteria in SumProduct, N/A Result dcd123 Excel Worksheet Functions 7 October 7th 05 01:26 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM


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