PDA

View Full Version : Adhoc SumIf on variable criteria



LLEW1
05-10-2009, 04:00 AM
Hi all, seeking help again from the experts. Cant find a solution in any thread for this one...I know one of you will be able to help though.

Example file attached to help explain request below:

Have two sheets <CriteriaAndSummary> and <Values>

CriteriaAndSummary has two sections the cells on the left are the variables that will call data in the Values sheet.

The Values sheet have currency amounts I need use, based on the Region as listed on the CriteriaAndSummary sheet - Each region shows individually on this sheet, so only one regions data is shown at any one time.

When it shows, I need sum the values for the Product (located on the Values sheet) as they exist for that region, and sum the $ values based on the number of sessions they appear on the Criteria Summary sheet; so with "Oranges" in the example this needs be summed for session a.b and c for the North Region.

While the amounts could be summed on the Values sheet, the Summary section on the CriteriaAndSummary sheet is driven by the data requested on that sheet, and might not call all the available instances on the Values sheet.

Through other code, the criteria data is called and populates the adhoc criteria section.

So I need code that can respond to that and placed the summed results back in the Summary section of the CriteriaAndSummary sheet.

Appreciate any help, thanks in advance...

mdmackillop
05-10-2009, 05:27 AM
Not sure that I follow you completely, but give this a try. I've made some minor data layout changes to simplify a solution.

LLEW1
05-10-2009, 02:00 PM
Hi "Mack" - your solution is really close, thanks - Im putting in place now to see if will suit fully.

Many thanks again - really appreciate help once more. Will let you know.