sumproduct with multiple criteria issue

[jw01]

hi there

having some difficulty with sumproduct and multiple criterias.

I have my revenue in column T "summary" sheet. I have the branch name in column "D" on summary sheet.

In the "offer" sheet, i have the "offered" revenue range.

in cell Y11 on the summary sheet, i would like to show what category the revenue falls under (cell range D8:I8 in offer sheet).

basically, for example,
Miami revenue = $80
therefore, in offer sheet, $80 falls between $83-$75 (i.e. List1 and List2). however, it should read as <$80 & >= $75, thus it becomes list 2.

I have attached a sample, if someone can pls help...ive been struggling for the half a day on this...pls and thxs.

FYI: if the price is less than "list 5" which is the bare minimum, then put "Less than".

[Richard Buttrey]

Hi

First put 'Less Than in J8 on the Offer sheet.

Then in Y11 on Summary copied down

Please Login or Register  to view this content.

[jw01]

Hi

First put 'Less Than in J8 on the Offer sheet.

Then in Y11 on Summary copied down

Please Login or Register  to view this content.

hello Richard buttrey

thanks for your prompt response. the formula is not working for some odd reason; it keeps saying "not listed"
in addition, there is no reference to the actual table in the offer sheet range D8:I13 (OFFSET(Offer!D$1:I$1,V11,0))

also, V11 is an empty cell?

if you can pls help. thx u

[FDibbins]

maybe this?

Please Login or Register  to view this content.

[benishiryo]

hi jw01. maybe you can provide a few more answers you are hoping to see? i don't know if i got your logic right here:

Please Login or Register  to view this content.

Edit: mine is an array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again

[jw01]

hi jw01. maybe you can provide a few more answers you are hoping to see? i don't know if i got your logic right here:

Please Login or Register  to view this content.

hello guys

thanks for the response however the formula is showing only "less than" etc.

to give another example, pls see below:
cell T16 (summary sheet) = $101.00 for New York (cell D16)
therefore, in the offer sheet i.e. range C9:I9 = new york
so $101 falls between $102-$96 or list 2 and list 3 (e.g. <102 & >=96), so the answer = list 4.

hope that makes sense?

thank u and thxs for your help; ive been on this for an entire day and getting no where

[jw01]

Branch Large deal List 1 List 2 List 3 List 4 List 5
New York $150 $120 $108 $102 $96 $90

in the above, the data should be seen as:

1) large deals are GREAT THAN EQUAL to i.e. >=$150
2) List 1: less than 150 and great than equal to 120
3) list 2: less than 120 and greater than equal to 108
4) list 3: less than 108 and greater than equal to 102
5) list 4: less than 102 and greater than equal to 96
6) list 5: less than 96 and greater than 90
7) LESS THAN: less than 90

hope that makes more sense?

[jw01]

hello guys

thanks for the response however the formula is showing only "less than" etc.

to give another example, pls see below:
cell T16 (summary sheet) = $101.00 for New York (cell D16)
therefore, in the offer sheet i.e. range C9:I9 = new york
so $101 falls between $102-$96 or list 2 and list 3 (e.g. <102 & >=96), so the answer = list 4.

hope that makes sense?

thank u and thxs for your help; ive been on this for an entire day and getting no where

Hello guys

OMG it works

thank you to benishiryo!!!!!!

[jw01]

hey guys

one final request:

would it be possible to count by "unique" party # in column G - see attached workbook.

for example, if G11 and G15 have same party #, therefore in Y15 = "repeat"?

is that possible?

[benishiryo]

glad to help. it's actually better if you start a new thread for a new question. and it's always good to type in manually what you wish to see because i can't link your unique count & "repeat" in Y15 together. unique count would be:
=SUMPRODUCT(1/COUNTIF(G11:G17,G11:G17))
that will give 6 counts

for "repeat" to be in Y15, try this in Y11 & copy down:
=IF(COUNTIF(G$11:G11,G11)>1,"Repeat","")