Creating Max If Formula with multiple conditions

**Zanenep** · 05-07-2012, 11:18 AM

First time actually posting an issue on here so thank you in advance for the help. What I have been wracking my brain on lately is the following:

I have a worksheet that has the following data: Community name, Date, and Water Usage for that day.

On a separate worksheet I have a row for each community to calculate their monthly accrual. What I am looking for is a formula that will return the Max usage between a specific date range (say 4/1/2012 and 5/1/2012) for a specific community. Thanks for any help I get and if there are any questions let me know.

Thanks!

**Fotis1991** · 05-07-2012, 11:29 AM

Hi and welcome to the forum.

So you need to count how match times exist a certain community between two days..

B1=START DATE

C1=END DATE.

So try this.

=SUMPRODUCT((A2:A5>=B1)*(A2:A5<=C1)*(B2:B5="A"))

Column A= dates

Column B= Names of communities.

**vandan_tanna** · 05-07-2012, 11:32 AM

How about using array equations?

Range "A1:C31" in sheet one has
name Date Water Usage
A 1-May 204
B 1-May 453
C 1-May 499
A 2-May 528
B 2-May 257
C 2-May 319
A 3-May 545
B 3-May 165
C 3-May 131
A 4-May 610
B 4-May 726
C 4-May 249
A 5-May 621
B 5-May 308
C 5-May 318
A 6-May 174
B 6-May 705
C 6-May 202
A 7-May 113
B 7-May 121
C 7-May 448
A 8-May 489
B 8-May 518
C 8-May 362
A 9-May 126
B 9-May 296
C 9-May 733
A 10-May 248
B 10-May 425
C 10-May 509

Then in Sheet 2:
Range A1:D2

Comm Start End Max
A 1-May 3-May 545

Where for max I used: {=MAX(IF(N(Sheet1!A2:A31=Sheet2!A2)*N(Sheet1!B2:B31<=Sheet2!C2)*N(Sheet1!B2:B31>=Sheet2!B2),Sheet1!C2:C31))}
note: instead of typing in curly brackets, use <ctrl><Shift><enter>

Above equation will be a lot more readable if you use named ranges.

**daddylonglegs** · 05-07-2012, 11:32 AM

Try this syntax

=MAX(IF((Community=Z1)*(Date>=Z2)*(Date<=Z3),Usage))

confirmed with CTRL+SHIFT+ENTER

where Z1, Z2 and Z3 contain a specific community name, start date and end date respectively