sumifs question

  • Thread starter Thread starter Jenny Barker
  • Start date Start date
J

Jenny Barker

=(SUMIFS('[Budget Spreadsheet with subaccount.xls]Data'!$F:$F,'[Budget
Spreadsheet with subaccount.xls]Data'!$G:$G,$B9,'[Budget Spreadsheet with
subaccount.xls]Data'!$A:$A,$A$1,'[Budget Spreadsheet with
subaccount.xls]Data'!$E:$E,"<="&'data input'!$A$1))

This SUMIFS function does exactly what it is supposed to do, assuming that
the user just wants data for a single fund located @ $A$1. However, now I
want to be able to pull data for multiple funds, which means there will have
to be some looping. I'm thinking of using the 'data input' sheet for user
input, but I'm not sure how to pull this off. Can someone give me a shove in
the right direction?
 
the SUMIFS function simply allows multiple AND conditions AFAIK, but what
you need a logical OR

ie give me the sum IF fund =this OR fund = that

i'd use addition, which isn't too onerous

for example
{=SUM( (A1:A100="X")*(B1:B100) ) }
would return the sum of column B where column A had X

{=SUM( (A1:A100="X")*(B1:B100) + (A1:A100="Y")*(B1:B100) ) }
would return the sum of column B where column A had X or Y
 
I'm not sure "or" is what I'm looking for. I want to do the sumifs
calculation for the value in $A$1, and add it to the sumifs for the value in
$A$2 if there is a value in $A$2, then $A$3, etc., until the loop reaches an
empty cell.

Patrick Molloy said:
the SUMIFS function simply allows multiple AND conditions AFAIK, but what
you need a logical OR

ie give me the sum IF fund =this OR fund = that

i'd use addition, which isn't too onerous

for example
{=SUM( (A1:A100="X")*(B1:B100) ) }
would return the sum of column B where column A had X

{=SUM( (A1:A100="X")*(B1:B100) + (A1:A100="Y")*(B1:B100) ) }
would return the sum of column B where column A had X or Y





Jenny Barker said:
=(SUMIFS('[Budget Spreadsheet with subaccount.xls]Data'!$F:$F,'[Budget
Spreadsheet with subaccount.xls]Data'!$G:$G,$B9,'[Budget Spreadsheet with
subaccount.xls]Data'!$A:$A,$A$1,'[Budget Spreadsheet with
subaccount.xls]Data'!$E:$E,"<="&'data input'!$A$1))

This SUMIFS function does exactly what it is supposed to do, assuming that
the user just wants data for a single fund located @ $A$1. However, now I
want to be able to pull data for multiple funds, which means there will
have
to be some looping. I'm thinking of using the 'data input' sheet for user
input, but I'm not sure how to pull this off. Can someone give me a shove
in
the right direction?
Click to expand...
Click to expand...
 
Back
Top