Opposite of Sumif??

Hi, is this possible you have an array formula instead of sumifs you can use "difference" by subtracting all array.. like for example below..

Sheet1
A B
1 Adrian 5.00
2 Adrian 6.00
3 Balcon 8.00
4 Balcon 9.00

Sheet2
A B
1 Adrian =b1-b2 of sheet1
2 Balcon =b3-b4 of sheet1

Is this formula can be done ? since b1 & b2 are in sheet1 in can be done with sumif by adding them but how about in subtraction, what could be the formula?

sorry for being noob about this..thanks in advance... Please see attached file.
sample_diff.xlsx
Here you can see 2 sheet tab list and diff tab..In List tab there is a column "well being transformation" that I have combined in order to get their difference of "economic_sufficiency" .. so its to be easy if you have 3 data list but if you have a thousand data list it will be worst because its time consuming...therefore I created the second tab which is "DIFF" but my problem is how gonna do this... please advise me on how to...

thanks....

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Im not really sure if this is what you want, but try this ARRAY formula, copied down...
=INDEX(LIST!$D$2:$D$7,MATCH(A2&2,LIST!$A$2:$A$7&LIST!$C$2:$C$7,0))-INDEX(LIST!$D$2:$D$7,MATCH(A2&1,LIST!$A$2:$A$7&LIST!$C$2:$C$7,0))

...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.

two sumifs also ??
=SUMIFS(LIST!$D$2:$D$7,LIST!$A$2:$A$7,DIFF!A3,LIST!$C$2:$C$7,2)-SUMIFS(LIST!$D$2:$D$7,LIST!$A$2:$A$7,DIFF!A3,LIST!$C$2:$C$7,1)

Vlad, OP profile indicates 2003, sumifS() wont work

I only looked at the attached file. I neglected to look @ the profile.

oops yes, you are correct

vanther, I notice that the file you uploaded indicates 2007 or later (.xlsx), but your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version

I'm missing a lot also of this unupdated profiles even with other forums..

Broken down, that formula finds the value from 1 instance of a name, finds the 2nd instance of that same name, and subtracts 1 from the other....

=INDEX(LIST!$D$2:$D$7,MATCH(A2&2,LIST!$A$2:$A$7&LIST!$C$2:$C$7,0))
-INDEX(LIST!$D$2:$D$7,MATCH(A2&1,LIST!$A$2:$A$7&LIST!$C$2:$C$7,0))

To give each duplicate name a unique ID, I just added 2 (or 1) to it, then combined the name in A and the Wave in C as the range

as far as the INDEX/Match combo is concerned, it is often used as INDEX/MATCH/MATCH, with the syntax of...

=index(criteria-to-find,column-to-search-in,0),row-number,column-number)
=match(criteria-to-find,column-to-search-in,0) 0 returns exact match
=MATCH(criteria-to-find,row-to-search-in,0)
soooo that gives us...
=index(criteria-to-find,match(criteria-to-find,column-to-search-in,0),match(criteria-to-find,row-to-search-in,0)

=Index(A1:E10,match("aaa",A1:A10,0),match(2013,A1:E5,0)

where A2:A10 contains a bunch of item names ("aaa", "BBB" etc)
and B2:B5 contains years, with 2013 being in D1...
that formula will return the value from D2 - the junction of A2 and D1

In your case, we only had 1 column to use, so there was no need for the 2nd MATCH

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)