sumproduct

[rcirone]

A B C D
1 NALM 100% NOB 0%
2 NOB 100% NOB 100%
3 NOB 0% NALM 0%

I am looking to find all the (NOB) in this sheet and them take all the % for the (NOB) and avgrage them out

There is 4 NOB and 4 % 2 are 100% and 2 are 0% the avgrage 50% I was trying to use sumproduct but it will not work please help thank you

 

[c_m]

Try:
=AVERAGE(AVERAGE(IF(A1:A3="NOB",B1:B3)),AVERAGE(IF(C1:C3="NOB",D1:D3)))
confirm with control+shift+enter

 

[rcirone]

thank you

 

[rcirone]

Re: sumproduct more help

A B C D E F
1 NALM 3456 100% NOB 3456 0%
2 NOB 3456 100% NOB 3456 100%
3 NOB 3456 0% NALM 3456 0%


How do I do the same thing but the cell I need are in row A , C AND D ,F and one thing how do I count the number if NOB i have does it have to be in another cell

 

[c_m]

Re: sumproduct more help

simply change the ranges:
=AVERAGE(AVERAGE(IF(A1:A3="NOB",C1:C3)),AVERAGE(IF(D1:D3="NOB",F1:F3)))
confirmed with CSE.
for your second question, did you mean count the occurence of NOB?

 

[rcirone]

yes (nob) and will that even work if I am pulling it from another tap in the workbook

 

[c_m]

to count NOB, try:
=COUNTIF(A1:F3,"NOB")

Yes, the formula should work if the ranges you are referring to are in another tab of the workbook.

 

[rcirone]

how do I put this in a cell that has nothing in it yet and so it will not show this #value! becouse sometime there will be info sometime not
=AVERAGE(AVERAGE(IF(A1:A3="NOB",B1:B3)),AVERAGE(IF(C1:C3="NOB",D1:D3)))

 

[rcirone]

What can I do to this this I am going to past this in a column and bring it down 100 row and reference another sheet but on the other some time the info is there some it is blank. How do past on one to reference another cell if the cell is blank with out it coming up as a #value! in the working cells

=AVERAGE(AVERAGE(IF(A1:A3="NOB",B1:B3)),AVERAGE(IF(C1:C3="NOB",D1:D3)))
<!-- / message -->