How to Countif when searching multiple sheets & criteria

September 18, 2018 at 10:48:24
Specs: Macintosh
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 31

13 Energy Crude 500 100 200 -75 450 -175

14 Currency Euro 300 200 250 -150 -100 100
15

Column 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 values

This 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



See More: How to Countif when searching multiple sheets & criteria

Reply ↓  Report •

#1
September 18, 2018 at 10:56:28
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


Reply ↓  Report •

#2
September 18, 2018 at 14:19:13
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     100 

Row 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


Reply ↓  Report •
Related Solutions


Ask Question