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