Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
I'm trying to do a sumifs with two criteria. Below is my data and equation.
ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
Hi
try =sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<$A$8),$B$2:$B$7) if this helps please click yes, thanks "CFitz" wrote: I'm trying to do a sumifs with two criteria. Below is my data and equation. ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
Yes that worked. I'm not too familiar with the SUMPRODUCT function though I
probably should be. Does anyone know why the SUMIFS won't work though? Seems like an odd bug with the function. That criteria works fine on it's own in a SUMIF. Thanks again! "Eduardo" wrote: Hi try =sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<$A$8),$B$2:$B$7) if this helps please click yes, thanks "CFitz" wrote: I'm trying to do a sumifs with two criteria. Below is my data and equation. ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
Hi,
once you start using it you will not be able to ride off it here you have more information about sumproduct http://www.xldynamic.com/source/xld.SUMPRODUCT.html "CFitz" wrote: Yes that worked. I'm not too familiar with the SUMPRODUCT function though I probably should be. Does anyone know why the SUMIFS won't work though? Seems like an odd bug with the function. That criteria works fine on it's own in a SUMIF. Thanks again! "Eduardo" wrote: Hi try =sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<$A$8),$B$2:$B$7) if this helps please click yes, thanks "CFitz" wrote: I'm trying to do a sumifs with two criteria. Below is my data and equation. ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
Hi
You are correct. It doesn't work. It seems as though the function doesn't want to deal with a second set of criteria, when it is text values. If you change A7 and A8 to 101 and 0, and have numeric values in columns D and E, then it works fine. I will take this up with some other colleagues, and see if I can find out more, and if this is still a problem I will report it as a bug. -- Regards Roger Govier "CFitz" wrote in message ... Yes that worked. I'm not too familiar with the SUMPRODUCT function though I probably should be. Does anyone know why the SUMIFS won't work though? Seems like an odd bug with the function. That criteria works fine on it's own in a SUMIF. Thanks again! "Eduardo" wrote: Hi try =sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<$A$8),$B$2:$B$7) if this helps please click yes, thanks "CFitz" wrote: I'm trying to do a sumifs with two criteria. Below is my data and equation. ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS Issue
I just tried this in 2003
=SUMIF(E2:E5,"<"&A8,B2:B5) and it returned 14., not 13 as it should. An array SUM(IF works as does SUMPRODUCT. So SUMIFS has just carried that forward. I suspected that would be where the problem was, but I don't know why but I suspect that the concatenation of < and 00 is getting <0, not <00. Changing E2:E5 to numeric values seems to confirm this. -- __________________________________ HTH Bob "CFitz" wrote in message ... Yes that worked. I'm not too familiar with the SUMPRODUCT function though I probably should be. Does anyone know why the SUMIFS won't work though? Seems like an odd bug with the function. That criteria works fine on it's own in a SUMIF. Thanks again! "Eduardo" wrote: Hi try =sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<$A$8),$B$2:$B$7) if this helps please click yes, thanks "CFitz" wrote: I'm trying to do a sumifs with two criteria. Below is my data and equation. ID COUNT SUM FIRST 4 LAST 2 010100 1 $40,000.00 0101 00 010101 7 $194,230.00 0101 01 010102 2 $60,000.00 0101 02 010201 4 $120,000.00 0102 01 0101 00 =SUMIFS(B2:B5,D2:D5,A7,E2:E5,"<"&A8) A7 = 0101 and A8=00 The IDs are formatted as text (to keep the leading zeroes). The equation is currently giving me 10 but it should be 9. When I change the second criteria to equals A8 it works and gives me 1. I've also made IF equations that check A7 & A8 against the First 4 & Last 2 columns and they behave as they should. This is only happens in the SUMIFS equation. Any ideas? Thanks Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumifs & Not Like | Excel Discussion (Misc queries) | |||
Sumifs | Excel Discussion (Misc queries) | |||
SUMIFS and OR | Excel Discussion (Misc queries) | |||
SUMIFS | Excel Discussion (Misc queries) | |||
SUMIFS and OR | Excel Worksheet Functions |