Multi Conditional formulas in Excel 2000

[graumann]

I have a spread sheet where I would like to count number of times two conditions are met.
The formula I am using works for a set value in the F column but I want to have a wildcard in there

=SUMPRODUCT(--(F4:F20="12/13*"),--(H4:H20="Charged"))

This formula shows a zero- when you remove the * it shows 1
Because the field can contain dates for example 12/13 to 12/15 I need a wild card. My formula shows only 12/13 exactly. And only if I remove the *.
How do I get it to look at all rows with the value starting with 12/13?

 

[Aladin Akyurek]

I have a spread sheet where I would like to count number of times two conditions are met.
The formula I am using works for a set value in the F column but I want to have a wildcard in there

=SUMPRODUCT(--(F4:F20="12/13*"),--(H4:H20="Charged"))

This formula shows a zero- when you remove the * it shows 1
Because the field can contain dates for example 12/13 to 12/15 I need a wild card. My formula shows only 12/13 exactly. And only if I remove the *.
How do I get it to look at all rows with the value starting with 12/13?

Wildcards are not allowed in formulas that operate on computed arrays like the ones with SumProduct. Try the following...

Make a list of dates that must hold for F4:F20 say in A4:A6 and use:


=SUMPRODUCT(--ISNUMBER(MATCH($F$4:$F$20,$A$4:$A$6,0)),--($H$4:$H$20="Charged"))

 

[Oaktree]

Welcome to the board!

One answer to your question is:
=SUMPRODUCT(--(left(F4:F20,5)="12/13"),--(H4:H20="Charged"))

But, that would mean your dates are stored as text. Why not store them as date and use =SUMPRODUCT(--(F4:F20=datevalue("12/13/2004")),--(H4:H20="Charged")) ?

 

[graumann]

Thanks for the help- it is a text field because they put in a range of values not just the one date.

 

[Aladin Akyurek]

Thanks for the help- it is a text field because they put in a range of values not just the one date.

Not sure what you're heading but you still either (1) convert F4:F20 to true dates and use the formula I posted or (2) keep the text-formatted dates in F4:F20 as is and modify the formula to:

=SUMPRODUCT(--ISNUMBER(MATCH($F$4:$F$20+0,$A$4:$A$6,0)),--($H$4:$H$20="Charged"))

A4:A6 must still house true dates of interest.