Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

formual request

I need to write a sumif formula, I attempted as you will see in the attachment highlighted in green.  didn't work, can you help.
Formula-II.xls
0
Pete Edwards
Asked:
Pete Edwards
  • 2
2 Solutions
 
Rgonzo1971Commented:
Hi,

What do you intend to sum?

Regards
0
 
Glenn RayExcel VBA DeveloperCommented:
It appears that you are trying to use the SUMIF function using three-dimensional referencing like so:
=SUMIF(Jan:Jul!B2:B100,B1,Jan:Jul!F2:F100)

Unfortunately, you can't use 3D referencing with the SUMIF function.

{Posted too quick!}

There are a couple of possible workarounds.  One might be to insert a SUMIF on each monthly sheet, then use 3D referencing to get those values.  However, I suspect that your master sheet - RF_OtherL - will have several rows of values in column B that you want totals for in column I, so this won't work.

The alternative (found here on YouTube) would be to create a SUMPRODUCT of INDIRECT references to the sheets.  

One would need an array of all the sheet names to make this work, say in column L (ex., L1 = "Jan", L2 = "Feb", and so on).  Then you can use this function:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$L$1:$L$6&"'!$B$2:$B$100"),B1,INDIRECT("'"&$L$1:$L$6&"'!$F$2:$F$100")))

Example file attached.

Regards,
-Glenn
EE-Formula-II.xls
0
 
Rgonzo1971Commented:
Hi,

If you want to keep it in the formula you could create the Month array within

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","DEC"}&"'!$B$2:$B$100"),B3,INDIRECT("'"&{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","DEC"}&"'!$F$2:$F$100")))
see cell I4

or create a named range with this data

={"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","DEC"}

=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!$B$2:$B$100"),B3,INDIRECT("'"&Months&"'!$F$2:$F$100")))
see cell I3


Regards
EE-Formula-III.xls
0
 
Pete EdwardsAuthor Commented:
Thanks.  I've been out of office, apologize for the late solution acceptance.

Good job, both will suffice my needs.

Take care
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now