sumproduct

rcirone

Active Member
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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try:
=AVERAGE(AVERAGE(IF(A1:A3="NOB",B1:B3)),AVERAGE(IF(C1:C3="NOB",D1:D3)))
confirm with control+shift+enter

thank you

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

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?

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

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.

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)))

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

Replies
5
Views
433
Replies
3
Views
184
Replies
1
Views
207
Replies
0
Views
101
Replies
1
Views
178

1,196,047
Messages
6,013,084
Members
441,747
Latest member
darkman77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back