Excel help needed.

[Bird222]

I need to sum cells that match two criteria that aren't in one single row or column. The spreadsheet has cells E1 through P1 numbered 1 through 12. There are certain rows that have a text string that I'm wanting to match to. Here's what I need. For any number I specify, any column that is less than or equal to the number and matches the row that has the text I specify will be summed. For example, let's say the entire area that the amounts are in is E24. The text criteria is in cells A2 and A4 and I want the values <=5 for those rows. I need a formula that is going to sum cells E2,F2,G2.H2,I2,E4,F4,G4,H4, and I4 TIA for any suggestions

 

[pete6032]

Not sure I totally understand what you're trying to do, could you post a screenshot of the spreadsheet? You should be able to do an IF statement, and you could potentially combine your two criteria into one cell using the ""&"" function to make your IF statement easier.

 

[IronWing]

The numerical criteria refers to the maximum column number to be summed, correct?

Naming the cells holding the criteria text and criteria number will help simplify and clarify the formulae as well.

 

[Bird222]

The numerical criteria refers to the maximum column number to be summed, correct?

Naming the cells holding the criteria text and criteria number will help simplify and clarify the formulae as well.

Yes.

 

[IronWing]

Yes.

That's hard. I'm pretty sure that you can use the HLOOKUP() function to build the formula. I'll see if I have time to play around with it today.

 

[Scarpozzi]

I would do this kind of processing out of Excel myself. Perhaps scripting languages or SQL, but I work with much larger data sets than this, typically (hundreds of thousands of rows/columns instead of just 12).

I'm still having trouble understanding the layout and formula goals. If you break down what you're trying to accomplish, you can basically create a validator that says which values should be summed... Use a formula for each value in the array that flags TRUE or FALSE on whether or not it's matched....then SUM only those values.


You can use search or find functions to compare values:
=ISNUMBER(SEARCH(A1,E1))
=ISNUMBER(FIND(A1,E1))
Just be mindful that some of those functions have wildcards in the logic, so it may match one character and may not be case sensitive.

After getting a block of TRUE and FALSE results, you can go through and use nested IF THEN logic for each field in the block. There may be a simpler, way to do it, but I still can't picture it in my head.

 

[Starbuck1975]

Do you PowerBI? You could bring your data table in and create a summed table filtered to multiple criteria.

 

[rcpratt]

If I'm understanding correctly...

SUMIFS formula for each column to add up the cells which include the desired text --> =SUMIFS(E:E, E:E, "*yourtext*")

Then another SUMIFS formula to add up each of the SUMIFS totals for each column that you want --> =SUMIFS(sumifsrange:sumifsrange, columnidrange:columnidrange, "<="&yournumber)

 

[Bird222]

Not sure I totally understand what you're trying to do, could you post a screenshot of the spreadsheet? You should be able to do an IF statement, and you could potentially combine your two criteria into one cell using the ""&"" function to make your IF statement easier.

Here is an example. I'm currently using a SUMIF formula but I have to have one for each row and add them together. I'm wanting one formula that can look at the array of cells and sum the ones that meet the two criteria.

 

[rcpratt]

Here is an example. I'm currently using a SUMIF formula but I have to have one for each row and add them together. I'm wanting one formula that can look at the array of cells and sum the ones that meet the two criteria.

You can’t do nested SUMIFS, so I’m not sure that it’s possible. Technically you could do it with nested IFs with this small of a data set, but I assume you want something that you could apply to a bigger data set. What’s the issue with the two step approach? Just hide the intermediate calcs.

 

[Brainonska511]

Can you make a 3rd column that is Yes/No if criteria are met/not met, then set up the SUMIF off that column?

 

[Bird222]

I'm wondering if SUMPRODUCT could be used in some way?

 

[rcpratt]

Good call.

=SUMPRODUCT(($D$2:$D$5=$A$8)*($E$1:$P$1<=$A$7)*$E$2:$P$5)

Where A7 is your number and A8 is your text.

 

[GoodRevrnd]

This question is phrased very confusingly and the dataset you gave doesn't help. I think you want to use INDIRECT + MATCH in the sum range to get the dynamic row lookup you're asking for.

 

[Bird222]

Good call.

=SUMPRODUCT(($D$2:$D$5=$A$8)*($E$1:$P$1<=$A$7)*$E$2:$P$5)

Where A7 is your number and A8 is your text.

Thanks. I'm going to try this when I get a chance. Question, what is column D? Is that where the text I'm trying to match is? Also can the formula be used with a wildcard? For example, 'text*more text'.

 

[rcpratt]

Sorry, yeah. Yours was in A:A.

 

[GoodRevrnd]

Thanks. I'm going to try this when I get a chance. Question, what is column D? Is that where the text I'm trying to match is? Also can the formula be used with a wildcard? For example, 'text*more text'.

=SUMPRODUCT(($D$2:$D$5=$A$8&"*")*($E$1:$P$1<=$A$7)*$E$2:$P$5)

 

[Bird222]

Thanks for the help. It works except it doesn't with the wildcard. The problem seems to be that SUMPRODUCT thinks the '*' is text instead of a wildcard.

 

[Tweak155]

Thanks for the help. It works except it doesn't with the wildcard. The problem seems to be that SUMPRODUCT thinks the '*' is text instead of a wildcard.

I think the code below will work (not the best, but it can be changed) on the screenshot file you attached a couple days ago. I tried to attach it to this post, but Anandtech filters it out.

The below will match text ignoring case, and it will be exact word match without the use of wildcards. It colors the numbers in yellow in which it is doing the calculation for to confirm it's doing the right ones.

To learn how to add a macro, see: https://www.excel-easy.com/vba/create-a-macro.html

Public Sub Bird222Request()
Range("A1:ZZ1000").Interior.Color = 16777215

Dim strMatch As String, dblTotal As Double, lngCol As Long, lngMaxNum As Long

strMatch = LCase(InputBox("Enter text to match: ", "Match Text"))

lngMaxNum = InputBox("Enter max number: ", "Max Number")

Dim rng As Range

For Each rng In Columns("A").SpecialCells(xlCellTypeConstants)
    If Trim(rng.Value) <> "" Then
        If LCase(rng.Value) Like strMatch Then
            For lngCol = 1 To lngMaxNum
                With Cells(rng.Row, "E").Offset(0, lngCol - 1)
                    dblTotal = .Value + dblTotal
                    .Interior.Color = vbYellow
                End With
            Next
        End If
    End If
Next rng

MsgBox "Answer: " & dblTotal
End Sub

 

[Bird222]

Thanks for the response but I have a working solution now.