Sumproduct formula to count multiple text

[sharky1978]

hi all

i'm trying to count various texts in cells for lost days, but am unable to get it to work correctly, below is what i have tried


=SUMPRODUCT((((($T$250:$T$282="Norfolk")*($Y$250:$AE$282="WEATHER"))+($Y$250:$AE$282="TRUCK BREAK DOWN")))+($Y$250:$AE$282="AGENCY CREW ISSUE"))

=SUMPRODUCT(((((T250:T282="Herefordshire")*(!$Y$250:$AE$282="WEATHER"))+($Y$250:$AE$282="TRUCK BREAKDOWN")))+($Y$250:$AE$282="AGENCY CREW ISSUE"))

below is the text that I'm trying to get it to count when they appear in a cell

WEATHER
TRUCK BREAK DOWN
AGENCY CREW ISSUE
UNIT BREAKDOWN
MATERIAL ISSUE
VELOCITY CREW ISSUE

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[sharky1978]

thanks ali

i have changed it hope this is better.

[JohnTopley]

Why are you reference ranges such as $T$250:$T$282 when there is no data ???

Guessing .....

=SUMPRODUCT(($T$250:$T$282="Norfolk")*(($Y$250:$AE$282="WEATHER")+($Y$250:$AE$282="TRUCK BREAK DOWN")+($Y$250:$AE$282="AGENCY CREW ISSUE")))

[CARACALLA]

Sheet1

T2=SUMPRODUCT((ISNUMBER(MATCH($K2:$Q2,List!$C$3:$C$13,0)))*(ISNUMBER(MATCH($K$1:$Q$1,$K$1:$O$1,0))))

Copy down

[sharky1978]

sorry it was the area of the excel sheet i was working on, as the early cells i done by manual input and im trying to automate the process and i copied over to a blank sheet so it wasn't to big

below was what i was trying

=SUMPRODUCT(($F$2:F33="Hereford")*(($K$2:$Q$34="WEATHER")+($K$2:$Q$34="TRUCK BREAK DOWN")+($K$2:$Q$34="AGENCY CREW ISSUE")))

[sharky1978]

thanks caracalla

this works to count them but i want it to only to count the ones for each specific contract 1.e hereford should have 5 in T14, cornwall should have 7 in T 6 and norfolk 6 in T10

[CARACALLA]

Sheet1

T2=SUMPRODUCT((ISNUMBER(MATCH($K$2:$Q$34,List!$C$3:$C$13,0)))*(ISNUMBER(MATCH($K$1:$Q$1,$K$1:$O$1,0)))*(TRIM(SUBSTITUTE(S2,"DAYS LOST",""))=$F$2:$F$34))

Copy down


I changed herefordshire to hereford

[sharky1978]

thanks for the reply that works as well,

is it possible just to get it T6,10,14 Without copying down as the cells above and below have different formulas in.

sorry for being a pain

thanks again for the help

[AliGW]

Only by copying and pasting.

[Glenn Kennedy]

So, you have only told us half the story!!

Put the appropriate formula back in T4, 5 & 7. Dlete all below T7. Select T4-T7 and drag down the column.