Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Here is my situation
I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=*A,SUM(I4:I29),0) =SUMIF(A4:H29, *A, I4:I29) =SUM(IF(((A4:A29=*A)+(B4:B29=*A)+(C4:C 29=*A)+ (D4:D29=*A)+(E4:E29=*A)+(F4:F29=*A ) +(G4:G29=*A)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,*A) Any suggestions? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
If a row contains more than one entry that meets the criteria should that be
calculated as n*I ? Like this: xxxA...xxxc...cccA...5 The row contins 2 entries that end in "A". So, should the result add 2*5 = 10 to the total? -- Biff Microsoft Excel MVP "Perry" wrote in message ... Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29="*A",SUM(I4:I29),0) =SUMIF(A4:H29, "*A", I4:I29) =SUM(IF(((A4:A29="*A")+(B4:B29="*A")+(C4:C29="*A") + (D4:D29="*A")+(E4:E29="*A")+(F4:F29="*A") +(G4:G29="*A")), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,"*A") Any suggestions? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Enter this in J4
=COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=*A,SUM(I4:I29),0) =SUMIF(A4:H29, *A, I4:I29) =SUM(IF(((A4:A29=*A)+(B4:B29=*A)+(C4:C 29=*A)+ (D4:D29=*A)+(E4:E29=*A)+(F4:F29=*A ) +(G4:G29=*A)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,*A) Any suggestions? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Hi,
First thing to note is that IF does not support wildcards. If there are at most one match per row or you want to sum it more than once if it appears a one row more than once you can use the single formula: =SUMPRODUCT(NOT(ISERR(SEARCH("?????A",A1:H12)))*I1 :I12) -- Thanks, Shane Devenshire "Sheeloo" wrote: Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=*A,SUM(I4:I29),0) =SUMIF(A4:H29, *A, I4:I29) =SUM(IF(((A4:A29=*A)+(B4:B29=*A)+(C4:C 29=*A)+ (D4:D29=*A)+(E4:E29=*A)+(F4:F29=*A ) +(G4:G29=*A)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,*A) Any suggestions? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Just to add to Shane's response.
If there are multiple cells in a single row that match that criteria, then the value in column I will be used for each match. ShaneDevenshire wrote: Hi, First thing to note is that IF does not support wildcards. If there are at most one match per row or you want to sum it more than once if it appears a one row more than once you can use the single formula: =SUMPRODUCT(NOT(ISERR(SEARCH("?????A",A1:H12)))*I1 :I12) -- Thanks, Shane Devenshire "Sheeloo" wrote: Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=*A,SUM(I4:I29),0) =SUMIF(A4:H29, *A, I4:I29) =SUM(IF(((A4:A29=*A)+(B4:B29=*A)+(C4:C 29=*A)+ (D4:D29=*A)+(E4:E29=*A)+(F4:F29=*A ) +(G4:G29=*A)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,*A) Any suggestions? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Thank you Shane and Dave. The SUMPRODUCT formula worked like a champ. I will
have to learn a bit more about this formula. Perry Hill "Dave Peterson" wrote: Just to add to Shane's response. If there are multiple cells in a single row that match that criteria, then the value in column I will be used for each match. ShaneDevenshire wrote: Hi, First thing to note is that IF does not support wildcards. If there are at most one match per row or you want to sum it more than once if it appears a one row more than once you can use the single formula: =SUMPRODUCT(NOT(ISERR(SEARCH("?????A",A1:H12)))*I1 :I12) -- Thanks, Shane Devenshire "Sheeloo" wrote: Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=รขฌย*Aรขฌย,SUM(I4:I29),0) =SUMIF(A4:H29, รขฌล*Aรขฌย, I4:I29) =SUM(IF(((A4:A29=รขฌย*Aรขฌย)+(B4:B29=รขฌย *Aรขฌย)+(C4:C29=รขฌย*Aรขฌย)+ (D4:D29=รขฌย*Aรขฌย)+(E4:E29=รขฌย*Aรขฌย )+(F4:F29=รขฌย*Aรขฌย) +(G4:G29=รขฌย*Aรขฌย)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,รขฌย*Aรขฌย) Any suggestions? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
A few keystrokes shorter:
=SUMPRODUCT((RIGHT(A4:H29)="A")*I4:I29) -- Biff Microsoft Excel MVP "Perry" wrote in message ... Thank you Shane and Dave. The SUMPRODUCT formula worked like a champ. I will have to learn a bit more about this formula. Perry Hill "Dave Peterson" wrote: Just to add to Shane's response. If there are multiple cells in a single row that match that criteria, then the value in column I will be used for each match. ShaneDevenshire wrote: Hi, First thing to note is that IF does not support wildcards. If there are at most one match per row or you want to sum it more than once if it appears a one row more than once you can use the single formula: =SUMPRODUCT(NOT(ISERR(SEARCH("?????A",A1:H12)))*I1 :I12) -- Thanks, Shane Devenshire "Sheeloo" wrote: Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29=?*A?,SUM(I4:I29),0) =SUMIF(A4:H29, ?o*A?, I4:I29) =SUM(IF(((A4:A29=?*A?)+(B4:B29=?*A?)+(C4:C 29=?*A?)+ (D4:D29=?*A?)+(E4:E29=?*A?)+(F4:F29=?*A ?) +(G4:G29=?*A?)), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,?*A?) Any suggestions? -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Just as a topic of conversation, this is a single formula, although no
pretty, that will total each match only once per row: =SUMPRODUCT((((RIGHT(A4:A29)="A")+(RIGHT(B4:B29)=" A")+(RIGHT(C4:C29)="A")+(RIGHT(D4:D29)="A") +(RIGHT(E4:E29)="A")+(RIGHT(F4:F29)="A")+(RIGHT(G4 :G29)="A")+(RIGHT(H4:H29)="A"))0)*I4:I29) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29="*A",SUM(I4:I29),0) =SUMIF(A4:H29, "*A", I4:I29) =SUM(IF(((A4:A29="*A")+(B4:B29="*A")+(C4:C29="*A") + (D4:D29="*A")+(E4:E29="*A")+(F4:F29="*A") +(G4:G29="*A")), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,"*A") Any suggestions? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
A few keystrokes shorter:
=SUMPRODUCT(--(MMULT(--(RIGHT(A4:H29)="A"),{1;1;1;1;1;1;1;1})0),I4:I29) Drawback: range is limited to no more than 5461 rows -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Just as a topic of conversation, this is a single formula, although no pretty, that will total each match only once per row: =SUMPRODUCT((((RIGHT(A4:A29)="A")+(RIGHT(B4:B29)=" A")+(RIGHT(C4:C29)="A")+(RIGHT(D4:D29)="A") +(RIGHT(E4:E29)="A")+(RIGHT(F4:F29)="A")+(RIGHT(G4 :G29)="A")+(RIGHT(H4:H29)="A"))0)*I4:I29) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29="*A",SUM(I4:I29),0) =SUMIF(A4:H29, "*A", I4:I29) =SUM(IF(((A4:A29="*A")+(B4:B29="*A")+(C4:C29="*A") + (D4:D29="*A")+(E4:E29="*A")+(F4:F29="*A") +(G4:G29="*A")), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,"*A") Any suggestions? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF?
Just a few?<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... A few keystrokes shorter: =SUMPRODUCT(--(MMULT(--(RIGHT(A4:H29)="A"),{1;1;1;1;1;1;1;1})0),I4:I29) Drawback: range is limited to no more than 5461 rows -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... Just as a topic of conversation, this is a single formula, although no pretty, that will total each match only once per row: =SUMPRODUCT((((RIGHT(A4:A29)="A")+(RIGHT(B4:B29)=" A")+(RIGHT(C4:C29)="A")+(RIGHT(D4:D29)="A") +(RIGHT(E4:E29)="A")+(RIGHT(F4:F29)="A")+(RIGHT(G4 :G29)="A")+(RIGHT(H4:H29)="A"))0)*I4:I29) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Enter this in J4 =COUNTIF(A4:H4,"*A") and copy down till J29 Now in I30 enter =SUMPRODUCT(--(J2:J290),I2:I29) to get what you want... I could not think of a way to combine the above two formulae -- If you find this post helpful pl. choose "Yes"... "Perry" wrote: Here is my situation I have a data range that contains a six-character value. I only care about the last character I want to sum the values in I4:I29 where the characters in A4:H29 meet a certain value EX: A4 = A00000A; I4 = 2 B10 = B11111A; I10=1 G5 = C12121A; I5 = 1 H29 = D12345A; I5 = 4 I want the sum of the values in I (8) for these cells but not for any others. I have tried: =IF(A4:H29="*A",SUM(I4:I29),0) =SUMIF(A4:H29, "*A", I4:I29) =SUM(IF(((A4:A29="*A")+(B4:B29="*A")+(C4:C29="*A") + (D4:D29="*A")+(E4:E29="*A")+(F4:F29="*A") +(G4:G29="*A")), SUM(I4:I29),0) =IF(RIGHT(A4:A29)="N",SUM(I4:I39),0) All of the above formulas return the value 0 (false) On the same worksheet the following formula works: COUNTIF(A4:H29,"*A") Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |