Hi, I had a question a few days back that I was able to get worked out. The formula works perfectly and my workbook does just what I want it to do. Now, I want to add another measure and I need help again!!

Here is what the 12 Monthly spreadsheets basically look like:

8

9 A B E G H I J K >>> AK

10 Sector Market Totals 1 2 3 4 5 3113 Energy Crude 500 100 200 -75 450 -175

14 Currency Euro 300 200 250 -150 -100 100

15Column E is the sum of G:AK which are days of the month (E13 is SUM(G13:AK13)

With that said, here is the formula that I came up with before...that works:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!b10:b61"),$B4, INDIRECT("'"&Months&"'!e10:e61")))

&Months& is the list of 12 tabs, Jan - Dec

B10:B61 are market names search criteria (not sure how to say this)

B4 is the cell of the market name that I am searching for.

E:10:E61 are the corresponding valuesThis gave me the total amount from all 12 months of profit/loss for every individual market.

This is what I want to add to my summary sheet:

&Months& is the list of 12 tabs, Jan - Dec

B10:B61 are market names search criteria (not sure how to say this)

B4 is the cell of the particular market name that I am searching for.If the search the formula finds Crude in B13, then I want to Count the number of positive days (> 0) in corresponding cells G13:AK13. In the scenario above, the answer would be 3 days >0.

I want to add all 12 months together and come up with total # of Winning days for each of the 46 markets for the year. (And total # of Losing days).

If there is no entry, then Idon't wamt to count it.I am not using Dollar amounts here. I am only looking for a count of winning days and eventually losing days, but I am guessing I would just have to change >0 to <0 in the formula.

I hope this makes sense and somebody can help me. I only have a basic understanding of the 1st formula. What I want to do here has me scratching my head!!

Thank you for any help!!!!!!!

Jerry

Please repost your data after reading the info found at the following How-To link.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hi,

I had a question a few days back that I was able to get worked out. The formula works perfectly and my workbook does just what I want it to do. Now, I want to add another formula and I need help again!!Here is what the 12 Monthly spreadsheets basically look like:

9 A B E G H I J K .... AK 10 Sector Market Totals 1 2 3 4 5 ..... 31 13 Energy Crude 500 100 200 -75 450 -175 14 Currency Euro 300 200 250 -150 -100 100Row 10 is Column Headings. G10:AK10 are days of the month

E13 = Sum(G13:AK13). This is the same format through E61 = sum(G61:AK61)With that said, here is the formula that I found before...it works:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Months&"'!b10:b61"),$B4, INDIRECT("'"&Months&"'!e10:e61")))

This gave me the total $ amount from all 12 months for the individual markets.

This is what I am trying to add to my summary sheet:

&Months& = the list of 12 tabs, Jan - Dec

B10:B61 = where the market names are searched (not sure how to say this)

B4 = market name from my Summary page that I am searching for. (i.e. Crude in this example)

B5 = Euro in the example (Cell reference would change for each market name)If the formula finds Crude in B13, then I want to Count the number of positive days (> 0) in corresponding cells G13:AK13. In the scenario above, the answer would be 3.

I want to add all 12 months together and come up with total # of Winning days for each of the 46 markets. (And total # of Losing days).

If there is no entry, then I don't want to count it.I am not trying to get Dollar amounts here. I am looking for a count of winning days and losing days. I am guessing I would just have to change >0 to <0 in the formula for losing days.

I hope this makes sense and somebody can help me. I only have a basic understanding of the 1st formula. What I want to do here has me scratching my head!!

Thank you for any help!!!!!!!

Jerry

message edited by jdueitt

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History