SUMPRODUCT two text are matched in the list

[shahidkhaki]

Hi

1- I have a problem. A column C have text "Temporary power is provided on date xxx" I only want to get the sumproduct of the of all with "Temporary" AND "AUH" in the columns. I do not want to test complete text "Temporary power is provided on date xxx" cause xxx is always changing.

Kindly, reply.

Shahid

[daddylonglegs]

If this is part of a larger SUMPRODUCT formula then perhaps

=SUMPRODUCT(--(ISNUMBER(SEARCH("AUH","temporary",C1:C100)))

although, for this criterion on its own you could use COUNTIF

=SUM(COUNTIF(C:C,{"AUH","temporary*"}))

[shahidkhaki]

Hi

Probably I miss-represent my question. Infact I have two columns.
Problem-1
Column A has "AUH, DXB, NE" and column C have text "Temporary power is provided on date xxx" I only want to get the sumproduct of the occurances of of "Temporary" in Column C AND "AUH" in the column A.

I do not want to test complete text "Temporary power is provided on date xxx" cause xxx is always changing.

Problem-2
I have Column A with "AUH, DXB, NE" and column D have only dates. I want to get the counts of "AUH" AND dates < today, also date say for June or dates in the range.

Please, help.

Regards
Shahid

[daddylonglegs]

OK for the first try

=SUMPRODUCT(--(A1:A100="AUH"),--ISNUMBER(SEARCH("temporary",C1:C100)))

adjust ranges as necessary but you can't refer to the whole column

then for your second query for a count of dates less than today

=SUMPRODUCT(--(A1:A100="AUH"),--(D1:D100<TODAY()))

for dates in June 2007

=SUMPRODUCT(--(A1:A100="AUH"),--(MONTH(D1:D100)=6),--(YEAR(D1:D100)=2007))

or for a count between any two dates, shown in cells H1 and H2

=SUMPRODUCT(--(A1:A100="AUH"),--(D1:D100>=H1),--(D1:D100<=H2))

[shahidkhaki]

Thanks Daddy

Both solutions are working. So kind of you.

Bye - Shahid