SUMIFS - VLOOKUP is not NA

[rachel06]

Hi!

Trying to get a formula to work if a vlookup is not NA.

I know it's something similar to this:

This is in a cell in C24 on a sheet called "Payment Grid"

=SUMIFS(C3:C22,B3:B22,not(isna(vlookup(B3,'Quarter Lookup'!B:C,2,0)

In simple terms: On the Payment Grid Sheet: I want to sum rows C3:C22 in cell C24 if the vlookup from cells B3:B22 to the quarter lookup is not NA.

Hope that makes sense? Maybe this isn't even possible but I feel like it should be somehow!

 

[Joe4]

Please show us an example of your data and expected results.

 

[rachel06]

Hi! This kind of fell off my radar. I don't know if I can submit anything other than tables of data - I'm on my work computer and can only do so much.

Anyway, the below formula is so close. It's summing certain cells in C3:C26, but it's summing the cells I DON'T want it to. The below is essentially written backwards.

I can try to post more information on this, but is there a way to re-write this so it sums the other cells in C3:C26?

=SUMPRODUCT(ISERROR(MATCH($B$3:$B$26,'Quarter Lookup'!$D$1:$D$53,0))*C3:C26)

 

[Joe4]

Without seeing your data and expected results, it is hard to say.
Can you please post an example of your data and expected results?

Even if it just screen prints, I really need to understand your data and see what it is you are after.

 

[rachel06]

The above is the payment grid sheet. I'm trying to sum the bolded lines. Those are bolded based on conditional formatting using a vlookup to the below (quarter lookup)

The formula I posted above is summing the lines with gray text rather than bold/black. I can work around it by subtracting the total sum of the column from the formula I posted above if that's the best solution.

 

[Joe4]

I am trying to piece this altogether, and it is very difficult without any row or column heading to see which cells your formula is referencing.
And where in either of those images are you putting your formula?
I don't see anything in your second image that looks like the sum of anything.

 

[afrazier1221]

It's hard to tell for sure exactly what you're after. But if the dashes represent a null value that would result in an N/A, then you could simply use a negative qualifier and do it backwards.

=SUMIFS(C3:C22,B3:B22,"<>"&"-")

This would add everything in Column C from Row 3 to Row 22 that does not correspond to a - in Column B, Row 3 to Row 22.

Hope that helps.

 

[rachel06]

Sorry! I'm usually in here asking questions about VBA, not formulas.

Let me try this again:

The below is the sheet I'm trying to, in row 28, sum the BOLDED rows for: (sheet name = Payment Grid)

Current formula is summing the non-bolded rows:

=SUMPRODUCT(ISERROR(MATCH($B$3:$B$26,'Quarter Lookup'!$D$1:$D$53,0))*C3:C26)

Those rows are bolded using conditional formatting/vlookup to non-NA values in the sheet below (Quarter Lookup) in columns C and D. So if it finds the values, the rows are bolded. If NA, they aren't bolded. Nothing is being summed on this sheet. This is the conditional formatting formula if that's important

=NOT(ISNA(VLOOKUP($B3,'Quarter Lookup'!$B:$C,2,0)))

I found a formula similar to the below on a 10 year old post here. It's very close to working, but as noted above, it's summing the opposite cells that I want it to:

=SUMPRODUCT(ISERROR(MATCH($B$3:$B$26,'Quarter Lookup'!$D$1:$D$53,0))*C3:C26)

This particular formula is in cell C28. Right now I have a workaround to just add "C27-" to the front of the formula, which takes the entire sum of column and subtracts the greyed out lines from it which seems to work okay, but still curious as to how to re-write the above formula to sum the opposite of what it's doing, if that makes sense

 

[Alex Blakenburg]

So what happens when you do this:

=SUMPRODUCT(NOT(ISERROR(MATCH($B$3:$B$26,'Quarter Lookup'!$D$1:$D$53,0)))*C3:C26)

Note: for MS365 you can use just Sum instead of SumProduct

 

[rachel06]

So what happens when you do this:

=SUMPRODUCT(NOT(ISERROR(MATCH($B$3:$B$26,'Quarter Lookup'!$D$1:$D$53,0)))*C3:C26)

Note: for MS365 you can use just Sum instead of SumProduct

This is perfect. I was trying to use NOT(ISNA and that wasn't working but this is perfect. Thanks so much!!!