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