If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMPRODUCT or SUMIF or ...
Hi,
I'm working in a worksheet that has: A B C D H 1 8 5 7 6 2 EXX GAL SHE EXX ... EXX 3 25 30 20 15 290 4 21 45 30 12 240 5 45 36 21 36 576 I need to put in the H column: (25*8)+(15*6)= 290 with the criteria EXX (21*8)+(12*6)= 240 " I think that I should use a SUMPRODUCT or a SUMIF but I don't know how Can you help with this?? Thanks |
#2
|
|||
|
|||
One way ..
Put in H3: =SUMPRODUCT(--(A$2$2=H$2),(A33)*(A$1$1)) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- "nfbelo" wrote in message ... Hi, I'm working in a worksheet that has: A B C D H 1 8 5 7 6 2 EXX GAL SHE EXX ... EXX 3 25 30 20 15 290 4 21 45 30 12 240 5 45 36 21 36 576 I need to put in the H column: (25*8)+(15*6)= 290 with the criteria EXX (21*8)+(12*6)= 240 " I think that I should use a SUMPRODUCT or a SUMIF but I don't know how Can you help with this?? Thanks |
#3
|
|||
|
|||
Thanks for your reply!
According to your formula H3 = 0 (don't understand why??) Note: between (A$2$2=H$2) and (A33), my excell doesn't let me put a , (comma) I had to put ; . "Max" escreveu: One way .. Put in H3: =SUMPRODUCT(--(A$2$2=H$2),(A33)*(A$1$1)) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- "nfbelo" wrote in message ... Hi, I'm working in a worksheet that has: A B C D H 1 8 5 7 6 2 EXX GAL SHE EXX ... EXX 3 25 30 20 15 290 4 21 45 30 12 240 5 45 36 21 36 576 I need to put in the H column: (25*8)+(15*6)= 290 with the criteria EXX (21*8)+(12*6)= 240 " I think that I should use a SUMPRODUCT or a SUMIF but I don't know how Can you help with this?? Thanks |
#4
|
|||
|
|||
Perhaps the text entries are not exactly the same; maybe one has 2 spaces
before it and another has 3. In J2 use this formula =A2=H2. This should return TRUE if the text entries are exactly the same. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nfbelo" wrote in message ... Thanks for your reply! According to your formula H3 = 0 (don't understand why??) Note: between (A$2$2=H$2) and (A33), my excell doesn't let me put a , (comma) I had to put ; . "Max" escreveu: One way .. Put in H3: =SUMPRODUCT(--(A$2$2=H$2),(A33)*(A$1$1)) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- "nfbelo" wrote in message ... Hi, I'm working in a worksheet that has: A B C D H 1 8 5 7 6 2 EXX GAL SHE EXX ... EXX 3 25 30 20 15 290 4 21 45 30 12 240 5 45 36 21 36 576 I need to put in the H column: (25*8)+(15*6)= 290 with the criteria EXX (21*8)+(12*6)= 240 " I think that I should use a SUMPRODUCT or a SUMIF but I don't know how Can you help with this?? Thanks |
#5
|
|||
|
|||
"nfbelo" wrote
Thanks for your reply! According to your formula H3 = 0 (don't understand why??) Note: between (A$2$2=H$2) and (A33), my excell doesn't let me put a , (comma) I had to put ; Perhaps try opening this sample file with the implemented formula which does return the results you indicated in your post: http://flypicture.com/p.cfm?id=52598 (Right-click on the link: "Download File" at the top in the page, just above the ads) File: nfbelo_wksht_1.xls (Think the conversion to your excel version should be automatic .. ) And if you still get zero in H3, as Bernard pointed out, there's every possibility of "invisible" extraneous spaces somewhere within the cells with the text "EXX", "GAL", etc, which might be throwing the matching off, so we could try with a TRIM wrapped around the range A22 and H2, i.e. put instead in H3: =SUMPRODUCT(--(TRIM(A$2$2)=TRIM(H$2)),(A33)*(A$1$1)) and copy H3 down as before .. Let us know how it went for you ! -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik atyahoodotcom ---- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | Worksheet Functions | 7 | July 1st, 2004 10:22 PM |
SUMIF vs SUM(IF(..)) vs SUMPRODUCT | Harlan Grove | General Discussion | 7 | July 1st, 2004 10:22 PM |
Sumif, Sumproduct or....? | Ricky | Worksheet Functions | 6 | June 17th, 2004 03:59 AM |
Multiple Criteria (Excel: SUMIF vs. SUMPRODUCT) | Cecilia | Worksheet Functions | 3 | January 23rd, 2004 07:25 PM |