Calculating two values of cells but displaying in numbers

[mark888]

Hi All,

Hope someone can help me. Attached is a sample excel file.

Mapping Calc.xlsx

Im having a difficulty in getting the value from sheet 2 column B because it contains spaces and it cant count the value of more than one field unit.
I tried using the below code but it does not grab the value because of spaces.

=SUMPRODUCT(('Sheet2'!B:B="DS")*('Sheet2'!A:A="successful")

Hope someone can either correct my formula in sheet 2 and sheet 1. :-)

Kind Regards,
Mark.

[:) Sixthsense :)]

In C4 cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Successful")*(TRIM(Sheet1!$B$1:$B$50)=B4))

Drag the above formula upto C14 Cell



In C15 cell

=SUMPRODUCT((Sheet1!$A12:$A$50="Successful")*(LEN(TRIM(Sheet1!$B$12:$B$50))=0))



In C16 Cell

=COUNTIFS(Sheet1!$A$1:$A$50,"Successful",Sheet1!$B$1:$B$50,"*DS*",Sheet1!$B$1:$B$50,"*TR*",Sheet1!$B$1:$B$50,"*GF*")

[mark888]

In C4 cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Successful")*(TRIM(Sheet1!$B$1:$B$50)=B4))

Drag the above formula upto C14 Cell



In C15 cell

=SUMPRODUCT((Sheet1!$A12:$A$50="Successful")*(LEN(TRIM(Sheet1!$B$12:$B$50))=0))



In C16 Cell

=COUNTIFS(Sheet1!$A$1:$A$50,"Successful",Sheet1!$B$1:$B$50,"*DS*",Sheet1!$B$1:$B$50,"*TR*",Sheet1!$B$1:$B$50,"*GF*")

Hi SixthSense,

Yes it works for column C4-C15 but C16 still does not work. Maybe I explained wrong. I need C6 to show in numbers how many fields in Sheet1 which has more than 1 field type instead of just DS TR and GF. Also do you have any formula to calculate for column D4 to D16 if I need the total of both successful and not successful? Sorry to add on more.

Kind Regards,
Mark.

[samba_ravi]

Please Login or Register  to view this content.

Try this and copy towards down

[samba_ravi]

D4=IF(B4="N/A",SUMPRODUCT(1*(TRIM(Sheet1!$B$2:$B$50)="")),IF(B4="Multiple Fields",SUMPRODUCT(1*ISNUMBER(SEARCH(" ",TRIM(Sheet1!$B$2:$B$50)))),SUMPRODUCT(1*ISNUMBER(SEARCH($B4,TRIM(Sheet1!$B$2:$B$50))))))

[mark888]

Please Login or Register  to view this content.

Try this and copy towards down

Hi Siva,

Unfortunately it doesnt work that way because I need the total in Sheet2 to be the exact number of rows in Sheet1. To be exact Sheet1 has 49 rows which means Sheet2 should have a total of 49 as well. The formula you provided calculates more than once for each cell. The one I need is for column C4-C15 must only contain data from Sheet1 Column B is there is only one value there. The ones with more than one value should be in Multiple Fields. :-)

Kind Regards,
Mark.

[:) Sixthsense :)]

Revised C16 Cell formula

=SUMPRODUCT((Sheet1!$A$1:$A$50="Successful")*(LEN(TRIM(Sheet1!$B$1:$B$50))<>LEN(SUBSTITUTE(TRIM(Sheet1!$B$1:$B$50)," ",""))))


In D4 Cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Not Successful")*(TRIM(Sheet1!$B$1:$B$50)=$B4))+C4

Drag the above formula upto D14 Cell


In D15 Cell

=SUMPRODUCT((Sheet1!$A12:$A$50="Not Successful")*(LEN(TRIM(Sheet1!$B$12:$B$50))=0))+C15


In D16 Cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Not Successful")*(LEN(TRIM(Sheet1!$B$1:$B$50))<>LEN(SUBSTITUTE(TRIM(Sheet1!$B$1:$B$50)," ",""))))+C16

Refer the attached file

[mark888]

Revised C16 Cell formula

=SUMPRODUCT((Sheet1!$A$1:$A$50="Successful")*(LEN(TRIM(Sheet1!$B$1:$B$50))<>LEN(SUBSTITUTE(TRIM(Sheet1!$B$1:$B$50)," ",""))))


In D4 Cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Not Successful")*(TRIM(Sheet1!$B$1:$B$50)=$B4))+C4

Drag the above formula upto D14 Cell


In D15 Cell

=SUMPRODUCT((Sheet1!$A12:$A$50="Not Successful")*(LEN(TRIM(Sheet1!$B$12:$B$50))=0))+C15


In D16 Cell

=SUMPRODUCT((Sheet1!$A$1:$A$50="Not Successful")*(LEN(TRIM(Sheet1!$B$1:$B$50))<>LEN(SUBSTITUTE(TRIM(Sheet1!$B$1:$B$50)," ",""))))+C16

Refer the attached file

Hi SixthSense,

This one works perfectly. Thank you very much to you and Siva for helping me out.

Kind Regards,
Mark.