Help with multiple COUNIFS & SUMIFS

[scott79]

Context:
I'm putting together a flexible template to combine samples into pools of various sizes and check that the pools fulfil certain criteria.



Problem:
I need to
Find the first instance of a pool number (in col. D)
Count the number of cells with the same pool number (in col. D) and display on the row of the first instance
Count the values in C divided by sum values in G for the cells that have the same value in D.
Max G/Min G for the cells that have the same value in D (in J).
Create a highlight/marked cell in K (for user data entry) for rows that have the first instance of a pool number in D
Value in G* value in J for rows that have the first instance of a pool number in D
Sum of M for rows that have the first instance of a pool number in D
Average of C for rows that have the first instance of a pool number in D


I started trying to build something flexible and got stuck so the spreadsheet is a bit of a mess.


Thank you in advance for any help you might be able to offer.

[José Augusto]

Try in F8 and copy down

Please Login or Register  to view this content.

[scott79]

Try in F8 and copy down

Please Login or Register  to view this content.

Thanks Jose, first part fixed!

[José Augusto]

Hi
Use in H8 adn copy down

Please Login or Register  to view this content.

Use in I8 an array formula (you must enter with CTRL+SHIFT+ENTER) and copy down
=IF(ISNUMBER(F8),MAX(--($D$8:$D$103=D8)*$G$8:$G$103)/MIN(IF(--($D$8:$D$103=D8)*$G$8:$G$103>0,$G$8:$G$103,1E+99)),"")
In J8 use and copy down

Please Login or Register  to view this content.

see the file

[scott79]

Hi Jose,
The I8 array formula seems to be working perfectly!
The other two formulas don't seem to be doing quite the right thing when I've pasted the formulas in.
The first one changes the value in H8 from 9.4 to 7.59 and I'm confident that 9.4 is the figure I'm looking for.
The formula for J8 changes the value from 124.2 to 2.4 and I'm confident that 124.2 is the figure I'm looking for.

Thank you very much for your help so far, you've been great.

[José Augusto]

Hi
D47 is 1, D48 is 2 ...
Is it correct ? So the count from d8:d103 gives me different.

[scott79]

Hi
D47 is 1, D48 is 2 ...
Is it correct ? So the count from d8:d103 gives me different.

Hi Jose, You are absolutely right about H, I apologise, I wasn't paying enough attention.
The formula for J should return 124.2 in J8 though. (45/0.36245)
Thanks again for your help, Scott.

[José Augusto]

You are welcome. Thanks for your feedback.
Don't forget to mark this thread as SOLVED

[scott79]

Hi Jose,

'In J8 use and copy down
=IF(ISNUMBER(F8),$K$8/I8,"")'

That part isn't working quite right, it should return a value of 124.2 in J8 but it is returning a value of 2.445.
This should lead to the value in cell M15 being equal to the manually entered value in cell K8.

Thanks,
Scott.

[scott79]

The formula it's replacing is =$K$8/MAX(G8:G16)

[José Augusto]

Try in J8 the array formula and copy down

Please Login or Register  to view this content.

[scott79]

Hi Jose,
I think we're nearly there but I would like the samples in pool 2 to reference K17, not K8 and pool 3 reference K21.
(J17 should be 221.3, not 497.9)
Is that possible?

Thanks again for your help, I know this is a long series of questions.

[José Augusto]

Try in J8 the array formula and copy down
Formula: Select Code copy to clipboard

Please Login or Register  to view this content.

$K8 instead $k$8

[scott79]

Try in J8 the array formula and copy down
Formula: Select Code copy to clipboard

Please Login or Register  to view this content.

$K8 instead $k$8

Perfect! Thanks!

[scott79]

Now I just need: The calculation in M to refer to the correct cell in J according to pool number.The calculation in Q to sum all cells in M with the same pool number (D)

1. The calculation in R to average all cells in C with the same pool number (D)

Thanks again, I definitely owe you a coffee!

[José Augusto]

Hi Scott
What version of Excel you are using?

I suppose you want this approach

See the the sheet 'Folha1" on file

[scott79]

Hi Jose,
I am using Excel for Mac version 15.18.

Folha1 looks very good but I need to be able to enter a maximum desired volume per pool (not N3 for all) and I need volume to add to pool (F) to reference the number defined for the pool.

Thank you very much for all your help so far, I really appreciate it.

[José Augusto]

Hi Scott

I include a new column with 'Maximum desired volume' that you must enter desired values. So, 'Max /Max desired' has a new formula.

How can I calculate the volume to add to pool 1?

See the file

[scott79]

Hi Scott

I include a new column with 'Maximum desired volume' that you must enter desired values. So, 'Max /Max desired' has a new formula.

How can I calculate the volume to add to pool 1?

See the file

Hi Jose,
You have calculated the volume to add to pool correctly! The lowest value in column C for each pool number D (e.g. C23 for pool 3) should result in that cell of row F (F23) being equal to the manually entered value in column O for that pool (e.g. O10 =10) which it is!
Thanks for all your help.
I think you've solved my problem.

[José Augusto]

Hi Scott

I'm happy to have helped and thank you for your feedback.

Do not forget to mark this thread as SOLVED.