SUMIFS, Multiple columns to sum, multiple criteria

[Padawan]

Greetings, Board. Long time, no see. I need some assistance with a SUMIFS formula, and/or changing the function I'm using.

Data is fairly straightforward, but across multiple columns. Col A = date, Col P = $Dollar Amount I need to sum, Col Q = String name I need to match (ie Sale-Auction). My problem is, I have 11 pairs of columns I need to sum/check against string name. So I also need to sum Col S, but compare it to text in Col T, sum Col V, compare to Col W, and 8 other pairs. And to clarify, I only need one summed number, so Col P, S, V et al are all summed together (all auction amounts).

Currently, I have a fairly large formula, =SUMIFS(P15:P2000, A15:A2000,">=" & AF11[A DATE], A15:A2000,"<=" & AF12 [A DIFFERENT DATE], Q15:1Q2000, "Sale-Auction") + then I have 10 other SUMIFS changing the 'P' and 'Q' ranges, and just connect these formulas with a plus '+' sign.

My gut tells me there is an array or SUMPRODUCT formula to use, but my brain cannot figure it out.

Help! Please!!

 

[pgc01]

Currently, I have a fairly large formula, =SUMIFS(P15:P2000, A15:A2000,">=" & AF11[A DATE], A15:A2000,"<=" & AF12 [A DIFFERENT DATE], Q15:1Q2000, "Sale-Auction")

+ then I have 10 other SUMIFS changing the 'P' and 'Q' ranges, and just connect these formulas with a plus '+' sign.

Hi

Please clarify

What do you change the ranges P and Q to?

The A and AF ranges do not change, right?

Please give examples.

 

[Padawan]

PGC,

Thank you for your reply. Sorry for my delay. I was away from my computer for a few days.


Columns A & AF. Correct. They do not change. Column A is the report date included in the data. Cell Input!$AF$11 is the lower date range limit. Cell Input! $AF$12 is the upper date range limit.

Controls!$E$26 through Controls!$E37 are the different criteria for the type of sale. The formulas are all identical except for changing the Controls! reference from $E$26 through $E$37. These formulas go in different columns.

Please see a complete formula below.

THANK YOU as always for your invaluable assistance.


=SUMIFS($P$15:$P$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$Q$15:$Q$2000,Controls!$E$26)+SUMIFS($S$15:$S$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$T$15:$T$2000,Controls!$E$26)+SUMIFS($V$15:$V$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$W$15:$W$2000,Controls!$E$26)+SUMIFS($Y$15:$Y$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$Z$15:$Z$2000,Controls!$E$26)+SUMIFS($AB$15:$AB$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AC$15:$AC$2000,Controls!$E$26)+SUMIFS($AE$15:$AE$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AF$15:$AF$2000,Controls!$E$26)+SUMIFS($AH$15:$AH$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AI$15:$AI$2000,Controls!$E$26)+SUMIFS($AK$15:$AK$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AL$15:$AL$2000,Controls!$E$26)+SUMIFS($AN$15:$AN$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AO$15:$AO$2000,Controls!$E$26)+SUMIFS($AQ$15:$AQ$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AR$15:$AR$2000,Controls!$E$26)+SUMIFS($AT$15:$AT$2000,$A$15:$A$2000,">=" & Input!$AF$11,$A$15:$A$2000,"<=" & Input!$AF$12,$AU$15:$AU$2000,Controls!$E$26)

 

[pgc01]

Hi

The first thing I'd try would be:

=SUMPRODUCT(($A$15:$A$2000>=Input!$AF$11)*($A$15:$A$2000<=Input!$AF$12)*($Q$15:$AU$2000=Controls!$E$26),$P$15:$AT$2000)

This assumes that your criterion string in Controls!$E$26 ("Sale-Auction" in your post #1 ) only appears in the columns where you test it (Q, T, W, ...).

If that's not the case there are other solutions.

 

[Padawan]

PGC,

I don't believe this will work. Col Q is a text column, "Sale-Auction' is one of the items. The dollar amountS associated with Col Q are in Col S. Similarly, Col T could also contain the string 'Sale-Auction' with the associated dollar amounts in Col V.

I do have a SUMPRODUCT formula virtually identical to count the number of occurrences of 'Sale-Auction', (Col Q, T, etc) but I need a formula to sum the dollar amounts in Columns P, S, V, Y, AB, AE, AH, AK, AN, AQ and AT.

Thank you!

 

[pgc01]

Hi Padawan

Sorry, not clear.

You tried and it did not work or you did not try it because you think that it will not work?

 

[Padawan]

PGC,

I both bow and yield sir! I did not believe the formula would work. I WAS WRONG. I have tried it and it is flawless. THANK YOU!!!

 

[pgc01]

I'm glad it helped. Cheers!