Compacting Multiple SUMIF formulas with multiple criteria

[dhosi439]

I am using the following formula to pull specific data out of a table based on the criteria 1Due through 26Due. Is it possible to rewrite the formula into one master formula from which I can reference and add 1 to the #Due value.

=SUMIF(BillChecklist[JAN],"1Due",BillChecklist[JANAMT])+SUMIF(BillChecklist[FEB],"1Due",BillChecklist[FEBAMT])+SUMIF(BillChecklist[MAR],"1Due",BillChecklist[MARAMT])+SUMIF(BillChecklist[APR],"1Due",BillChecklist[APRAMT])+SUMIF(BillChecklist[MAY],"1Due",BillChecklist[MAYAMT])+SUMIF(BillChecklist[JUN],"1Due",BillChecklist[JUNAMT])+SUMIF(BillChecklist[JUL],"1Due",BillChecklist[JULAMT])+SUMIF(BillChecklist[AUG],"1Due",BillChecklist[AUGAMT])+SUMIF(BillChecklist[SEP],"1Due",BillChecklist[SEPAMT])+SUMIF(BillChecklist[OCT],"1Due",BillChecklist[OCTAMT])+SUMIF(BillChecklist[NOV],"1Due",BillChecklist[NOVAMT])+SUMIF(BillChecklist[DEC],"1Due",BillChecklist[DECAMT])

For example.

Master Formula would have 1Due written into the formula in Cell A1, then in B1 I would simply need to write =A1+1.

At the moment I am using 26 of the formula above each the same except for the number preceding the word Due.

I was attempting to use the DSUM formula with SUM but am unable to get multiple criteria and fields to add up.

Any suggestions?

 

[Gerald Higgins]

Let's simplify. . .
Let's say you have this. . .

=SUMIF(BillChecklist[JAN],"1Due",BillChecklist[JANAMT])

I think you'll find that this will also work just the same. . .

=SUMIF(BillChecklist[JAN],A1&"Due",BillChecklist[JANAMT])

where A1 contains the value 1, or whatever.

Does that help ?

 

[dhosi439]

That can work. I was looking to avoid using so many SUMIF formulas. I am using 12 formulas in each of 26 cells. Any suggestions?

 

[Gerald Higgins]

It's difficult to see how you can use fewer SUMIF formulas, without seeing how your data is laid out.

Do you really need to have Billchecklist[Jan] as a separate range from Billchecklist[Feb] and so on ?
It might be better to just have one huge Billchecklist, allowing you to use just one SUMIF.
You could also set up a range with non-contiguous elements - for example A1:A10 and C1:C10 (and so on), and name that as Billchecklist[All] or something