Log In
Register
Home
New Member
August 4, 2019
Offline
Hi All
i have a problem
please give me solution..
i have a data with 10 columns and 10 rows
i want to return answer in 11th column
in answer column i want to return column(s) ,which having a particular value(s) in same row.
when i am using sumproduct formula like this
=SUMPRODUCT((A6:J6=>25)*(COLUMN(a6:J6)))
IT GIVES RESULT CORRECT BUT WHEN ONLY ONE COLUMN PASSED MY QUESTION(VALUE=>25)
IF MORE THAN ONE COLUMN HAVING VALUE=>25, THIS FORMULA ADD ALL COLUMNS AND GIVE SUM.
BUT I WANT THEN SEPARATELY.
is any solution .
PLEASE HELP ME
THANK IN ADVANCE
July 16, 2010
Offline
Hi Oot,
Welcome to our forum!
It's difficult to visualise what you want without a sample Excel file, but I'd say you're best to use a PivotTable for this as SUMPRODUCT by design, sums the data that meets the criteria.
Please upload a sample Excel file with your data and desired result so we can see what you're working with and provide you with the best solution.
Mynda
New Member
August 4, 2019
Offline
Thanks Mynda..
i have a table like this
| Title | A | B | C | D | E | F |
| Month 1 | 1 | 2 | 3 | 2 | 6 | 0 |
| Month 2 | 1 | 2 | 4 | 4 | 5 | 6 |
| Diff | 0 | 0 | -1 | -2 | 1 | 6 |
I Give a name to Row Titles ="Titles"
and Diff row = "Diff"
With Text Join(array) formula i.e. Textjoin(",",,if((Diff)<>0,Titles,"")
i got result "C,D,E"
i want to get column heading(With Comment), in which difference is not equal "0",
Diffrence in Column: "C Deducted,D Deducted,E Increased,F Applied"
Hope now easliy understand my problem..
Thanks in advance
July 16, 2010
Offline
Hi Oot,
Thanks for the further info. It's still not completely clear. e.g. Column F is also <> 0 but you didn't mention it in your "i got result C,D,E" example.
However, I'm guessing you want a text string that collates the column differences and adds the text 'Deducted' or 'Increased'. I'm assuming you don't have Office 365 with the new dynamic array formulas. In which case TEXTJOIN won't sort them for you. I recommend you add another row to your table that joins the column letter and the text 'Deducted' or 'Increased'. e.g.
=B1&" "&IF(B4<0,"Deducted","Increased")
Then use TEXTJOIN to make your text string of results.
Next time, please upload a sample Excel file as it will make it easier for both of us.
Mynda
1 Guest(s)
