Solved: If with an OR or &

[JimS]

The following Array Formula works perfect:

{=SUM(IF(FREQUENCY(IF(Status="Won",IF(OppN<>"",MATCH(OppN,OppN,0))),ROW(OppN)-ROW(Cell2)+1),PSRev))}

It currently looks for all the records in my dataset that have a Status = "Won".

Can it be modified to look for 2 different Statuses? I need it to sum if the Status is = "Won" or if the staus is = "Booked".


Something like the following formula (but this formula sums "all" of the records):
{=SUM(IF(FREQUENCY(IF(OR(Status="Won",Status="Booked"),IF(OppN<>"",MATCH(OppN,OppN,0))),ROW(OppN)-ROW(Cell2)+1),PSRev))}

I tried a "and" in place of the "or" but that didn't work at all.

Any ideas?

Thanks...

JimS

[Bob Phillips]

Can you post your workbook?

[JimS]

I've removed everything that does not apply.

The cell in Red on the Summary tab is what I'm trying to get to work.

It should only sum the data in the T column (on the Data tab) once for each record.

You will see that there are multiple entries (rows) with the same data. These multiple rows make up a record. Each 1 should only get sum'd once.

The data looks odd only becasue of all the data I removed.

Thanks...

JimS

[Bob Phillips]

Two ways you can do it

=SUM(IF(FREQUENCY(IF((Status="Booked")+(Status="Won"),IF(OppN<>"",MATCH(OppN,OppN,0))),ROW(OppN)-ROW(Cell2)+1),PSRev))

or

=SUM(IF(FREQUENCY(IF((Status={"Booked","Won"},IF(OppN<>"",MATCH(OppN,OppN,0))),ROW(OppN)-ROW(Cell2)+1),PSRev))

Can I ask what you think the formula is doing, as their is one very unusual aspect of it to my mind?

[JimS]

Thanks for the help!

As far as what I think the formula is doing and what it actually is doing is a great mystery...

Someone on the VBA Express Forum helped me out with this formula before. Everyone produces some pretty amazing answers on this site but half the time (at least for me) I do not really understand how the formulas work.

There can be several rows with the same OppN (column F) and this formula is supposed to sum the PSRev (column T) only once if there are multiple rows with the same number in column F.

[Bob Phillips]

There can be several rows with the same OppN (column F) and this formula is supposed to sum the PSRev (column T) only once if there are multiple rows with the same number in column F.

That is exactly what it does do, that was the thing that I found a bit odd. But it obviously does what you want :)