countif nesting formula 2

[Spott]

I have a report I'm trying to build. I pull the data from an access query and am summarizing it in excel. My plan is for it to auto update everything when I refresh the data.

I think I need to nest a formula, and it's giving me a headache!. This formula shows all entires with "*sigma*", I also need it to look at Column A on the sheet and only count column E if Column A = "D1" (the shift at work), I'd then copy the working formula for "D2", "N1", "N2". The variations I have tried either add the D1 & Sigma or multiply them.

=COUNTIF('Metals L2'!E$2:E$1132,"*sigma*&quot

Thanks for your help!

 

[bilko1]

I think I understand what you're trying to do and I would use an array formula. Try this:

=Count(if('metals L2'!E2:E1132="*sigma*",if('metals L2'!A2:A1132="D1",0)))

To make this an array hold down Ctrl & Shift and then Enter. The formula won't work if you just enter.

There's probably another way you can write this but off the top of my head.....

Let me know if this helps.

 

[xlbo]

=SUM(('metals L2'!E2:E1132="*sigma*&quot*('metals L2'!A2:A1132 = "D1&quot) - use ctrl+shift+enter not just enter as it needs to be an array formula. Don't worry about the "sum" - Array formulae generally evaluate true or false so if E2 = *sigma*, a 1 is returned. If A2 = "D1", another 1 isreturned. 1 * 1 = 1. Therefore if either of the cells is FALSE, a 0 is returned 1*0 = 0 therefore no entry. The sum just sums up all the ones et viola - your answer (I hope)
Have a look at

http://j-walk.com/ss/excel/tips/tip74.htm

for some other examples of summing and counting with multiple criteria

 

[xlbo]

=SUM(('metals L2'!E2:E1132="*sigma*&quot*('metals L2'!A2:A1132 = "D1&quot) - use ctrl+shift+enter not just enter as it needs to be an array formula. Don't worry about the "sum" - Array formulae generally evaluate true or false so if E2 = *sigma*, a 1 is returned. If A2 = "D1", another 1 isreturned. 1 * 1 = 1. Therefore if either of the cells is FALSE, a 0 is returned 1*0 = 0 therefore no entry. The sum just sums up all the ones et viola - your answer (I hope)
Have a look at

http://j-walk.com/ss/excel/tips/tip74.htm

for some other examples of summing and counting with multiple criteria
HTH
Geoff

 

[Spott]

Well...so far no dice, but I appreciate the help!
I tried both formulas w/CSE and get 0. Result should be 6.
Here is what I entered:
=COUNT(IF('Metals L2'!E$2:E$999="*sigma*",IF('Metals L2'!A$2:A$999="D1",0)))
=SUM(('Metals L2'!E$2:E$999="*sigma*&quot*('Metals L2'!A$2:A$999="D1&quot)

I'm checking out the tip page now, to see if any of the variations will work.

 

[SkipVought]

Hi Spott,
Not sure if I am understanding either, but here goes...

1. I created a new column comcatenating A & E in column G

2. Here's the formula...

=COUNTIF(G2:G999,"*Sigma*"&D1)

If D3 = 3, then every time col A is 3 AND col E is "Sigma" it counts.

Is that what you are looking for? Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Spott,

Seeing as you've not been able to resolve the problem, might you consider using (with my help) Excel's "database functions". This includes both formulas that can produce a summary matrix, and an option to extract the data (to a separate sheet) that generates any of the summary totals.

Excel's database functions are often overlooked, but they indeed can be VERY useful. Perhaps this is a situation where they can be put to good use.

If you want to consider this alternative, I would ask that you email me a file that contains the field names and a small chunk of data. This way, I can be "specific" in "tailoring" the solution to fit your needs.

If there is a concern with "sensitive data", simply replace it with "dummy data".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bilko1]

Hi
Sorry my solution didn't work. I use that exact formula myself and it works perfectly. Not sure why it's not happening for you.
Sorry again