A report has been generated in excel, there is a column that describes the type of signal it puts out (24,120,etc) i need to be able to identify all the 24 signals and then add up how long the wiring is for all those. so in other words, search column A for a specific term(24,120,etc) then add the column B for all those that have 24, another for 120, etc..
hope this is clear haha
Hi,
You need to use SUMIF.
SO for example, =SUMIF(A2:A30,24,B2:B30)
Just to note the range in column A needs to be the exact same as column B for it to work!
Try using the SUMIF() function, similar to this
Assumptions:
Column A has your values in A2:A100 (or as many as you need).
Column B will have the 24 wiring signal types (13, 120, etc) in cells B2:B25.
Column C in will have the wiring length needed for those corresponding signal types.
Enter this formula in cell C2: =SUMIF( A2:A100, ">", B2 )
Then copy that formula from C2 and paste it into cells B3 to B25. (Or copy down C2 )
By having the wiring signal types in Column B, your wiring length formulas in column C are more easily adaptable.
and you have a visual defence as to which result goes with which type.
Hope this helps
If this has been helpful, please click on the star at the left.
the only problem is the range has both integer and text(24DC) how can i incorporate this?
could you attach a workbook with representative sample data? and the desired result
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Hi,
So are you searching for 24DC or is there various variations of text after 24?
Here a small sample showing how to count each type, where 24, 24DC and 24Ac would each be different types.
I apologize for an error in my previous answer to you.
The starting formula in cell C2 should have been: =COUNTIF( $A$2:$A$100, ">" & B2 )
In the example, the list of measures in column A has been defined as a range with name 'Measures'.
This simplifies the formula even more and allows for easy expansion or deletion of entries in the 'Measures' area without adjusting the formula.
Last edited by StuCram; 09-12-2016 at 11:34 AM. Reason: wrong function (Had SUMIF but it should have been COUNTIF)
i was able to achieve my desire result with the function =SUMIF(F:F,"*120VAC*",O:O) which sums the number of terminations a wire has within the criteria desired(120VAC)
however when i use the same command format but for column C using =SUMIF(F:F,"*120VAC*",C:C) it returns a value 0 when it shouldnt, any ideas what could cause this?
It depends on the data in column C. Is it numeric?
It would help to see the workbook/worksheet in order to check.
all the data is numeric in column C i also tried to attach a sample file but wasn't able to browse files
also worth noting, the values in column C are being populated with the following command [=IFERROR(VLOOKUP(B18, Sheet2!B:S, 18, FALSE),"")]
...would this maybe cause some error?
Last edited by EXCELsior44; 09-13-2016 at 08:33 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks