Help w/unique nesting of countif statement

New Member
I use a countif statement and find the number of times a cell contains a value in an entire sheet.
Now each time it does find the value I want it to Sum the \$ amount of these occurances that is shown in another cell. Each time the value appears, the formula would have to look in the previous column. If that cell is formatted as currency that is the value. If it is not it needs to keep moving down (a maximum of 4 cells) one cell at a time until it finds a currency value and then that is it. Does anyone have any ideas?

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Scott Huish

MrExcel MVP
You're saying you have a COUNTIF, but your description sounds like you want the first value where the adjacent cell is formatted as currency.

A COUNTIF would imply that you would want to count every time this was the case.

What are you trying to do exactly?

How does this maximum of 4 cells requirement fit into it?

New Member
Difficult to explain, lets say I have a sheet with multiple groups of cells that have info in them. A group is 10 cells wide x 10 cells high. In column 10 I have 65 possible entries that can be in these columns. In column 9 at the bottom of the column is a dollar amount that applies to this whole 10 cell x 10 cell group. I want to know how much revenue is associated with each of the different 65 entries. Make sense? If it sees entry "X" 10 times in the entire sheet, then there should be 10 values of \$ that go with it. X was responsible for this amount of money. Is this easier to understand?

Scott Huish

MrExcel MVP
It sounds like you could use a pivot table.

But formulawise, could you not use a SUMIF?

Replies
12
Views
387
Replies
2
Views
42
Replies
5
Views
103
Replies
3
Views
107
Replies
1
Views
208

1,191,723
Messages
5,988,308
Members
440,148
Latest member
sandy123

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