conditional summing

[JoshuaSQ]

I am trying to determine how to do a conditional sum with conditions such as: if a value is unique, if the value = x, and if the value is the max/min...

I think i struggle with the "if unique" condition the most, i guess i just don't understand how it works.

in any case, i am providing an attachment that *hopefully* describes my objectives a little better.

As always, any help is appreciated. Thanks!

josh

[NBVC]

The green cells match up to non-unique values in column A and in Column B...

so why are those getting summed?

Are you looking for one formula or separate formulas?

Not to clear.

[JoshuaSQ]

Perhaps "unique" is the wrong word. Maybe "only count it once" would be more accurate. I only want to sum 1 value from each of the different Org values... so if there are three unique values, there should be three numbers being summed (assuming "factorX" exists for each of them).

I am looking for a single formula, however, the formula will be replicated for each "factor" so there may end up being 6 or so...

Does that help explain it?

[martindwilson]

=MAX(IF((A4:A22="Org01")*(B4:B22="FACTOR2"),C4:C22,""))
confirmed with control +shift+enter
will return max for org1 factor 2
then add them together so

Please Login or Register  to view this content.

and confirm with control +shift+enter
so it looks like this

Please Login or Register  to view this content.

gives totals for max org1,2,3 factor2

[NBVC]

I haven't yet been able to come up with a single formula that will do the whole trick, but if you add a couple of helper columns (one for Min and one for Max) and use formulas to get those values... then a single formula to extract the final numbers you want is achieved...

see attached...

[JoshuaSQ]

That would in fact work for the example i have provided, however, the actual application of this will be handling much more data. So to specify "A4:A22="Org01"" is not favorable.

NBVC, a helper column would be acceptable, i was just beginning to consider that as well. I imagine the helper column would resolve the issue of min/max but i still struggle determining how to handle the "count only once" problem.

Any thoughts

[NBVC]

See the attached...columns D and E... If the ORG and Factor are duplicated, the min/max number will show up in the same row as the last occurrance of the repeats... the other rows remain blank.

[JoshuaSQ]

Oh, great, i didn't realize your last post had the attachment.

I think this works!

Thank you!

[NBVC]

Great!

Can you also please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save