Sumifs/Max with many conditions

I have a list of territories in Mexico, along with a unique identifying number and a value attached to it.
I need the Sumif and / Or Max function (or whatever function will do the job ) to return the highest value of the uniquely identifying account as long as the text WS features in the territory name.

So for P0009/2018 that would be 492,222
P00010/2018 would be 1,478,283

In addition, I would need the formula to ignore all entries with the text "Mexico - Quintana Roo" and "Mexico - Baja California"

After that I would need the exact same as above, but instead of the text WS to be present I want to isolate those with EQ (so either those with EQ in it on its own or as well as with WS)


Hope this explains it!

table.JPG

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Try something like this:

=MAX(IF((A$2:A$12<>"Mexico - Quintana Roo")*(A$2:A$12<>"Mexico - Baja California")*(B$2:B$12="P0009/2018"),C$2:C$12)) Ctrl Shift Enter

After that I would need the exact same as above, but instead of the text WS to be present I want to isolate those with EQ (so either those with EQ in it on its own or as well as with WS)

Try something like this:

=MAX(IF(ISNUMBER(FIND("EQ",A$2:A$12))*(B$2:B$12="P0009/2018"),C$2:C$12)) Ctrl Shift Enter

I seem unable to upload attachments - it may be blocked by my IT policy.

After reading post #5 I see that what you asked for in post #1 is not the same as what you want.

but there are other territories in my master list I dont want to include, so just the Mexican territories (which all begin with "Mexico -")

What is your question here? What about the Mexican territories?

You specify P0009 in your formula, how is it going to sum up the "max" figure of all the other P number identifiers?

Are you saying that you want to find the max value for each unique identifier and take the sum of all of those?

I suggest following the steps in post #2 and uploading a small representative sample workbook along with the desired results (which you can enter manually) based on the sample data.

Correct, rereading my initial post I wasnt clear. My list of territories are of many countries all over the world. The ones I am interested in summing up are for Mexican territories that are not QR or BC.

Yes exactly as you put it, find the max value of each unique identifier and take the sum of those.

I have to get home to upload a representative workbook as it seems the upload document facility is blocked here at work.

here is detailed instruction: post#2

spreadsheet attached

Here's one way using 2 helper columns:

K4 =IF(AND(MAX(IF((C$4:C$30=C4)*(LEFT(B$4:B$30,6)="Mexico")*(ISNUMBER(FIND("WS",B$4:B$30))),D$4:D$30))=D4,COUNTIFS(D$4:D4,D4,C$4:C4,C4)=1),D4,"") Ctrl Shift Enter
Drag down through K30

L4 =IF(AND(MAX(IF((C$4:C$30=C4)*(LEFT(B$4:B$30,6)="Mexico")*(ISNUMBER(FIND("EQ",B$4:B$30))),D$4:D$30))=D4,COUNTIFS(D$4:D4,D4,C$4:C4,C4)=1),D4,"") Ctrl Shift Enter
Drag down through L30

F4 =SUM(K:K)

H4 =SUM(L:L)

thanks! I bow down to your awesome skills...

is there any way to do this without the need for a helper colomn? also is there a provision in there to exclude all "Mexico - Quintana Roo" and "Mexico - Baja California" entries?

Does your real data actually have instances where the value in column B is repeated for a givevn value in column C? The sample workbook doesn't and based on that it appears you could just use this formula:

=SUMIFS($D$4:$D$30,$B$4:$B$30,"Mexico*WS*",$B$4:$B$30,"<>Mexico - Quintana Roo*",$B$4:$B$30,"<>Mexico - Baja California*")

Originally Posted by 63falcondude

the O.P. is looking to only sum the maximum value for each group in column C, not all values.

That was the bit I overlooked - I thought it was the max value per territory (there's only one relevant item for each of those).

There must be a better formula, but this seems to work:

=SUMPRODUCT((COUNTIFS($C$4:$C$30,$C$4:$C$30,$B$4:$B$30,"Mexico*WS*",$D$4:$D$30,">"&$D$4:$D$30)=0)*(ISNUMBER(SEARCH("Mexico*WS*",$B$4:$B$30)))*(NOT(ISNUMBER(SEARCH("Mexico - Quintana Roo",$B$4:$B$30))))*(NOT(ISNUMBER(SEARCH("Mexico - Baja California",$B$4:$B$30)))),$D$4:$D$30/IF(COUNTIFS($D$4:$D$30,$D$4:$D$30,$C$4:$C$30,$C$4:$C$30,$B$4:$B$30,"Mexico*WS*",$B$4:$B$30,"<>Mexico - Quintana Roo*",$B$4:$B$30,"<>Mexico - Baja California*"),COUNTIFS($C$4:$C$30,$C$4:$C$30,$B$4:$B$30,"Mexico*WS*",$D$4:$D$30,$D$4:$D$30,$B$4:$B$30,"<>Mexico - Quintana Roo*",$B$4:$B$30,"<>Mexico - Baja California*"),1))

@rorya

Close! But it includes D21 (because D20 and D21 are both the max for group P00017/2018). Only one max per group should be included.

Forgot to mention you have to array enter it (so that it excludes the duplicate values!).

Aah I see. Good job! Rep added.

Hopefully the O.P. is okay with this solution.

I'd still go with helper columns personally!

maybe something like this (just for fun with PowerQuery )

rorya, your formula worked its magic - thank you so much
63falcondude and sandy666 thank you so much for your hard work also....

rep added to you all

You are welcome and thanks

Have a nice day

darn it - it doesnt seem to work on my full listing. Please see attached. I have filtered the data by MEXICO*WS* and highlighted in yellow the numbers I was expected to see summed up in P2. About $9.45m. But the formula produced $6.675m. Where does it go wrong?

Rorya - have you had a moment to look at my latest problem with the formula?

It was excluding Baja California Sur as it matched Baja California. Try this one instead:

=SUMPRODUCT((COUNTIFS($E$4:$E$218,$E$4:$E$218,$D$4:$D$218,"Mexico*WS*",$N$4:$N$218,">"&$N$4:$N$218)=0)*(ISNUMBER(SEARCH("Mexico*WS*",$D$4:$D$218)))*(NOT(ISNUMBER(SEARCH("Mexico - Quintana Roo",$D$4:$D$218))))*(NOT(ISNUMBER(SEARCH("Mexico - Baja California,",$D$4:$D$218)))),$N$4:$N$218/IF(COUNTIFS($N$4:$N$218,$N$4:$N$218,$E$4:$E$218,$E$4:$E$218,$D$4:$D$218,"Mexico*WS*",$D$4:$D$218,"<>Mexico - Quintana Roo*",$D$4:$D$218,"<>Mexico - Baja California,*"),COUNTIFS($E$4:$E$218,$E$4:$E$218,$D$4:$D$218,"Mexico*WS*",$N$4:$N$218,$N$4:$N$218,$D$4:$D$218,"<>Mexico - Quintana Roo*",$D$4:$D$218,"<>Mexico - Baja California,*"),1))

brilliant that works very well!! The formula works great for the Mexico. I have now run into a problem with applying the logic to New Zealand, where it is to exclude Auckland, Wellington and Canterbury...see attached (last one I promise but I am pulling my hair out trying to solve it!)

See spreadsheet attached, the formula I have bastardized is in P2...the expected result is in Q2

where have i gone wrong?

Where do you get your expected result from? It makes no sense to me.

Let me try again i think the source data was wrong with the original file.

I have now highlighted the items I am expecting the formula to pick up, also some comments as to why those numbers are to be used over others.

hope it makes sense now...

I'm not seeing any highlights or comments in that file?

@rorya

Sorry - uploaded an older version....try this one

Your data goes down to row 314 but the formula is only looking as far as row 218, that's all.

@rorya

a good spot - formula updated but its still short of the expected number.

There was a logic error. Try this one:

=SUMPRODUCT((COUNTIFS($E$4:$E$298,$E$4:$E$298,$D$4:$D$298,"New Zealand*",$D$4:$D$298,"<>New Zealand - Auckland",$D$4:$D$298,"<>New Zealand - Canterbury, Christchurch",$D$4:$D$298,"<>New Zealand - Wellington, Hutt/Porirua",$N$4:$N$298,">"&$N$4:$N$298)=0)*(ISNUMBER(SEARCH("New Zealand*",$D$4:$D$298)))*(NOT(ISNUMBER(SEARCH("New Zealand - Auckland",$D$4:$D$298))))*(NOT(ISNUMBER(SEARCH("New Zealand - Canterbury, Christchurch",$D$4:$D$298))))*(NOT(ISNUMBER(SEARCH("New Zealand - Wellington, Hutt/Porirua",$D$4:$D$298)))),$N$4:$N$298/IF(COUNTIFS($N$4:$N$298,$N$4:$N$298,$E$4:$E$298,$E$4:$E$298,$D$4:$D$298,"New Zealand*",$D$4:$D$298,"<>New Zealand - Auckland",$D$4:$D$298,"<>New Zealand - Canterbury, Christchurch",$D$4:$D$298,"<>New Zealand - Wellington, Hutt/Porirua"),COUNTIFS($E$4:$E$298,$E$4:$E$298,$D$4:$D$298,"New Zealand*",$N$4:$N$298,$N$4:$N$298,$D$4:$D$298,"<>New Zealand - Auckland",$D$4:$D$298,"<>New Zealand - Canterbury, Christchurch",$D$4:$D$298,"<>New Zealand - Wellington, Hutt/Porirua"),1))

Once again, I would really recommend rethinking this approach. A pivot table would probably help speed things up!