Multi Conditional formulas in Excel 2000

graumann

New Member
Joined
Jan 18, 2005
Messages
4
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
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

New Member
Joined
Jan 18, 2005
Messages
4
Thanks for the help- it is a text field because they put in a range of values not just the one date.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,148,526
Messages
5,747,211
Members
424,068
Latest member
Salim khamis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top