COUNTIFS Trouble

Guys,

I'm trying to count my sales teams units. However I'm having a difficult time inputting a COUNTIFS formula

If there are two people tied to a sale, I want to be able to give them a credit of 1/2 versus a full amount if the names are different in two columns.

For example, since Jack split a deal on a new item, I want to be able to count 1/2 of it since the column over has another sales person tied to it.

data.PNG


Thanks in Advance guys.

Can you attach a spreadsheet?

See the attached item.

I'm stumped. I'm trying to do this, but it's coming out as 3 and not 3.5

=COUNTIFS($A$2:$A$12,"NEW",$B$2:$B$12,"JACK")+COUNTIFS($A$2:$A$12,"NEW",$C$2:$C$12,"JACK")-((COUNTIFS($A$2:$A$12,"NEW",$B$2:$B$12,"JOAN")+COUNTIFS($A$2:$A$12,"NEW",$C$2:$C$12,"JOAN"))*0.5)

Never mind - I wasn't accounting for the 'used' in my check. So this would work.

There's got to be a more elegant way to do it though.

In the used column: =COUNTIFS($A$2:$A$12,"USED",$B$2:$B$12,"JACK")+COUNTIFS($A$2:$A$12,"USED",$C$2:$C$12,"JACK")-(((COUNTIFS($A$2:$A$12,"USED",$B$2:$B$12,"JOAN")+COUNTIFS($A$2:$A$12,"USED",$C$2:$C$12,"JOAN")))*0.5)

Stupid question, in the MMULT array, does it have to be in the same page of excel? I'm on a different tab and replacing your input and I'm not having any success. I'm going to try another sheet and post in a second to see if you can help.

Nevermind, for some reason substituted the formula =SUM(MMULT(($B$2:$C$12="JACK")*($A$2:$A$12="NEW"),{1;1}/2)) when I replaced "JACK" with a cell number i.e. A2 it didn't work. It's working and pulling numbers now but I'm not sure if it's right still...

Ok, I tried handwriting it, and copied the formula. I'm not sure if I completely understand the Array portion. I tried copying the format, but I should've indicated that I am pulling this not from another workbook, but same workbook, different sheet. I can't seem to get it to go. Can you help with this? See attached.

I'm not quite clear what you are trying to do, since your 'sheet2' has 'John Smith', so I just referenced exactly the same stuff, just indicating the sheet in the formula. If you input this in Sheet2 A2 it should work.

{=SUM(IF((Sheet1!$B$2:$C$12=Sheet1!$D3)*(Sheet1!$A$2:$A$12=Sheet1!E$1),1/(MMULT(--(Sheet1!$B$2:$C$12<>""),{1;1}))))}

Let me try this. I have a dashboard on my excel sheet, the first sheet pulls data dumps from the second page. Sorry if I wasn't more clear just very anxious to get this done. Thanks a lot for all your help guys!

Thanks so much, that worked! You guys are awesome. I had some user errors that were holding me back.