# Multi Conditional formulas in Excel 2000

#### graumann

##### New Member
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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

##### MrExcel MVP
graumann said:
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

##### MrExcel MVP
Welcome to the board!

=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

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

##### MrExcel MVP
graumann said:
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.

Replies
0
Views
80
Replies
0
Views
115
Replies
2
Views
289
Replies
2
Views
145
Replies
6
Views
226

1,181,785
Messages
5,932,037
Members
436,815
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back