Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculate sum of numbers with conditions
I need to calculate the sum of number dependate on 2 conditions. One
condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#2
|
|||
|
|||
Curtis,
An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#3
|
|||
|
|||
Try
=SUMPRODUCT((A1:A100=123)*(MONTH(B1:B100)=5),C1:C1 00) 123 = employee number month = may (5th month) c1:c100 is the range you want to sum if above 2 conditions are true on the same row. "Curtis" wrote in message ... I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#4
|
|||
|
|||
SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536)
this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#5
|
|||
|
|||
In answering your question the amount the month is to be calculated from a
date----the answer is yes. each row in the sheet has a date assigned to it which could represent any day of any month. Hope this helps and thanks for your help "Curtis" wrote: SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#6
|
|||
|
|||
DId really do it unless I am doing something wrong or did not give you enough
info. SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well keeping in mind that each row in the sheet has a date assigned to it which could represent any day of any month of any year. "Dave R." wrote: Try =SUMPRODUCT((A1:A100=123)*(MONTH(B1:B100)=5),C1:C1 00) 123 = employee number month = may (5th month) c1:c100 is the range you want to sum if above 2 conditions are true on the same row. "Curtis" wrote in message ... I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#7
|
|||
|
|||
=SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR
_05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#8
|
|||
|
|||
Doesn't work. However it could be my fault as I might not be relaying all the
information. The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) , look at a specific tech number (column c), look at the speciic tech number ( column b) and then sum up the $$ for that tech for that month. The days are listed in column c, and can include any date) Hope that helps ce "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#9
|
|||
|
|||
I noticed a typo
s/b The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) , look at a specific tech number (column c), look at the speciic month ( column b) and then sum up the $$ for that tech for that month. The days are listed in column b, and can include any date) "Curtis" wrote: Doesn't work. However it could be my fault as I might not be relaying all the information. The formulae has to look at an entire sheet ('Raw - 2nd'!$B$2:$B$65536) , look at a specific tech number (column c), look at the speciic tech number ( column b) and then sum up the $$ for that tech for that month. The days are listed in column c, and can include any date) Hope that helps ce "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#10
|
|||
|
|||
Why is this giving me an error? It doesn't make sense!!!!
SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw - 2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536) Column c is employee number Column b is date range Column j is $$ range "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) |
#11
|
|||
|
|||
1. Change Raw - 2nd to Raw2nd.
2. In B37 enter the first day date of a month/year of interest. For example: 1-Apr-05 Then invoke: =SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536) The range you apply the formula to is too big. If it must be that big, consider switching to a SumIf formula for efficiency... In K2 enter and copy down: =C2&"#"&B2-DAY(B2)+1 Then invoke: =SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd!$ J$2:$J$65536) Curtis wrote: Why is this giving me an error? It doesn't make sense!!!! SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw - 2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536) Column c is employee number Column b is date range Column j is $$ range "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#12
|
|||
|
|||
I used your second suggestion
=SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&1-Apr-5,Raw2!$J$2:$J$65536) and it returns a value of 0 which is not correct. Note: the formula =C2&"#"&B2-DAY(B2)+1 was entered in p2 and copied down. The worksheet has other info. Also the totals are being capture in a different sheet in the workbook thanks "Aladin Akyurek" wrote: 1. Change Raw - 2nd to Raw2nd. 2. In B37 enter the first day date of a month/year of interest. For example: 1-Apr-05 Then invoke: =SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536) The range you apply the formula to is too big. If it must be that big, consider switching to a SumIf formula for efficiency... In K2 enter and copy down: =C2&"#"&B2-DAY(B2)+1 Then invoke: =SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd!$ J$2:$J$65536) Curtis wrote: Why is this giving me an error? It doesn't make sense!!!! SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw - 2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536) Column c is employee number Column b is date range Column j is $$ range "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#13
|
|||
|
|||
The $A37&"#"&1-Apr-5 bit as such won't work.
Either put 1-Apr-05 in, say, B37 and invoke: =SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&$B$37,Raw2!$J$2 :$J$65536) or express 1-Apr-05 directly as DATE(2005,4,1) and invoke: =SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&DATE(2005,4,1), Raw2!$J$2:$J$65536) Curtis wrote: I used your second suggestion =SUMIF(Raw2!$C$2:$C$65536,$A37&"#"&1-Apr-5,Raw2!$J$2:$J$65536) and it returns a value of 0 which is not correct. Note: the formula =C2&"#"&B2-DAY(B2)+1 was entered in p2 and copied down. The worksheet has other info. Also the totals are being capture in a different sheet in the workbook thanks "Aladin Akyurek" wrote: 1. Change Raw - 2nd to Raw2nd. 2. In B37 enter the first day date of a month/year of interest. For example: 1-Apr-05 Then invoke: =SUMPRODUCT(--(Raw2nd!$C$2:$C$65536=$A37),--(Raw2nd!$B$2:$B$65536-DAY(Raw2nd!$B$2:$B$65536)+1=$B37),Raw2nd!$J$2:$J$6 5536) The range you apply the formula to is too big. If it must be that big, consider switching to a SumIf formula for efficiency... In K2 enter and copy down: =C2&"#"&B2-DAY(B2)+1 Then invoke: =SUMIF(Raw2nd!$C$2:$C$65536,$A37&"#"&$B37,Raw2nd !$J$2:$J$65536) Curtis wrote: Why is this giving me an error? It doesn't make sense!!!! SUMPRODUCT(--('Raw - 2nd'!$C$2:$C$65536=$A37),--(MONTH('Raw - 2nd'!$b$2:$b$65536=4),'Raw - 2nd'!$J$2:$J$65536) Column c is employee number Column b is date range Column j is $$ range "Bob Phillips" wrote: =SUMPRODUCT(--(APR_05!$C$2:$C$1000=$A37),--(MONTH(APR_05!$D$2:$D$1000=7),APR _05!$J$2:$J$1000) -- HTH RP (remove nothere from the email address if mailing direct) "Curtis" wrote in message ... SO far I have =SUMIF(APR_05!$C$2:$C$65536,$A37,APR_05!$J$2:$J$65 536) this sums up the $$ by employee number. I need to add a formula that pulls the $$ by the month as well . "Matt Lunn" wrote: Curtis, An array formula would work here. Could you please give a little bit more detail about your data? You mention days. Does this mean the month is to be calculated from a date? Thanks, Matt "Curtis" wrote: I need to calculate the sum of number dependate on 2 conditions. One condition is emplyee number ( in a column of many numbers) and the other is the month (in a column listed with many days) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate values in multiple values with multi conditions | Excel Worksheet Functions | |||
Calculate Minimum numbers | Excel Worksheet Functions | |||
How do I automatically calculate YTD numbers by changing a date? | Excel Worksheet Functions | |||
How do I calculate sum based on 3 conditions? | Excel Worksheet Functions | |||
Formula to calculate only the negative numbers | Excel Worksheet Functions |