Formula to sume if 2 different factors are needed

R

riverad

New Member
Joined
Apr 9, 2014
Messages
21
Hi Guys,

I have a big list with names, money spent and date.
I need somehow with a formula to do:

take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname...
Im gonna upload a photo, so you can understand better what i mean.

[TABLE="width: 707"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Money
[/TD]
[TD][/TD]
[TD]Date
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD="align: right"]5
[/TD]
[TD][/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Daniel
[/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Daniel
[/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Domi
[/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD="align: right"]20
[/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Domi
[/TD]
[TD="align: right"]04.03.2014
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]etc
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Domi
[/TD]
[TD="align: right"]12
[/TD]
[TD][/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]etc
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Domi
[/TD]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD]etc
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Domi
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD="align: right"]04.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Domi
[/TD]
[TD="align: right"]6
[/TD]
[TD][/TD]
[TD="align: right"]04.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fede
[/TD]
[TD="align: right"]8
[/TD]
[TD][/TD]
[TD="align: right"]01.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fede
[/TD]
[TD="align: right"]16
[/TD]
[TD][/TD]
[TD="align: right"]02.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Camila
[/TD]
[TD="align: right"]34
[/TD]
[TD][/TD]
[TD="align: right"]02.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Camila
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Camila
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD="align: right"]03.03.2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




I need the list of the left to produce me list of the right somehow, at least just the sume of everything spent everyday.

thank you all for your efforts

wish u a sunny day =)

Domi
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
You need the SUMIFS function. If names are in col A, amounts in col B, dates in col C you have the list of names in col D and dates in row 1 going across: in cell E2 you'd have
=SUMIFS($B2:$B100,$A2:$A100,$D2,$C2:$C100,E$1)
First parameter is what to some, then for each condition, you have range1, condition1,range2,condition2 and so on

If you have Excel before 2007, it's a lot harder. You need the SUMPRODUCT function. But let me know if that's the case.
 
Upvote 0
Hi Domi
Welcome to the board

A Pivot Table will give you directly what you want.
Just create one from the table on the left and it will give you the table to the right.
 
Upvote 0
Johnny C said:
You need the SUMIFS function. If names are in col A, amounts in col B, dates in col C you have the list of names in col D and dates in row 1 going across: in cell E2 you'd have
=SUMIFS($B2:$B100,$A2:$A100,$D2,$C2:$C100,E$1)
First parameter is what to some, then for each condition, you have range1, condition1,range2,condition2 and so on
Click to expand...

Thanks for your help, im almost done.

I could do it with this form, but i cant understand how to choose the second condition, that would be the date....
without the date it works for me:

=SUMIF(A:A;"Daniel";B:B)

The only thing that remains is splitting the above result into every date.... tried a couple of other functions but couldnt make it work.

Thank you again,

Regards,

Domi
 
Upvote 0
So actually what im trying to do: or how it should be after my logic:

A: Names
B: Money Spent
C: Date

SUMIF(A:A;"Daniel",C:C;"01.03.2014";B:B)

But somehow, insertig the come to give a second condition wont work....

any help please? =D

ty
 
Upvote 0
That's because you're using SUMIF. You need the function SUMIFS

SUMIF works with one condition only, and was the only simple option up until XL 2007.

SUMIFS works with multiple conditions. It has the format
=SUMIFS(Sum_Range, Condition1_Range,Condition1,[Condition2_Range,Condition2,...]) where you need a Condition1 and can add (I think) 8 conditions.

The limitation on SUMIFS is that all the ranges need to be columns or rows and the same size.

There are equivalent formulae for COUNT (COUNTIFS) and AVERAGE (AVERAGEIFS) that let you use multiple conditions.

If you need to use multiple conditions but on a combination of rows, columns of possily different sizes or in versions of Excel pre-2007, you need the SUMPRODUCT function... but using it is difficult as it's got some tricky concepts like vector arrays that you need to understand, best avoid it until you absolutely need it.
 
Upvote 0
Johnny C said:
That's because you're using SUMIF. You need the function SUMIFS

SUMIF works with one condition only, and was the only simple option up until XL 2007.

SUMIFS works with multiple conditions. It has the format
=SUMIFS(Sum_Range, Condition1_Range,Condition1,[Condition2_Range,Condition2,...]) where you need a Condition1 and can add (I think) 8 conditions.

The limitation on SUMIFS is that all the ranges need to be columns or rows and the same size.
Click to expand...

THank you a lot, hahaha i just figured out how to do it, even with criterias and criteriaranges on other sheets =D

I just one more quick lil question.

If i have a table full of negative and positive numbers, and i need ALL numbers to be positive on the table, is there any excel function to do this???

THank you again a loooot

=D im really happy i managed to do it.

Kind regards,

domi
 
Upvote 0
It depends on whether you want to add up only the positive numbers or all as if they were positive

=SUMIF(
#VALUE!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Search data and calculate with VBA UserForm
Replies
0
Views
2K
JustoneE
J
1
  • Question Question
VBA excel SUMPRODUCT/SUMIF/INDIRECT formula equivalent or other option needed to collate and summarise worksheet data from across 3 workbooks?
Replies
1
Views
915
1Thess521
1
P
  • Question Question
COUNTIFS formula.
Replies
4
Views
493
Peter h
P
M
  • Question Question
Selectively order data from specific customer on a new sheet, effectively creating a mini invoice of a few columns
2
Replies
10
Views
1K
milonius
M
A
  • Question Question
Macro Help - Copying/Summing/Averaging data from a Master Sheet to Various Months.
Replies
1
Views
443
Ajw1986
A

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top