# COUNTIFS AND OR in MSexcel: therefore SUMPRODUCT AND OR(?)

#### Dee Dee

##### New Member
I am having an issue with the "old" version excel still. Each new addition to a formula seems to make things more difficult. In this case, I need to make L, M and N involve multiple criteria with AND and OR.

Formal complaint tracker a:a =<180 AND J:J="N" AND T:T="N"
OR if A:A>360 AND J:J="Y" OR T:T="Y"

This would be for L and I can figure our the other two. Again, I am having issues with my xl2bb paste. I really do not know what I am doing wrong because it had been working before.

Note:
The first few formulas I did on this file it was about 430kb, after it was returned to me for additional work it is now 45,220kb. I assume that this may be a part of the problem.

Tracker new version.xlsx
ACDEFGHIJKLMNO
1As of :Carryover from prior FYFiled in Current FYTotal (B+C)Pending AcknowledgmentPending Acceptance or DismissalAcceptedDismissedAmendedPending InvestigationInvestigation Completed TimelyInvestigation UntimelyAverage Days in InvestigationSettlement
2
3
4FY202200000000000
5FY20213362021120 1
6FY2020661263210271
7FY201900000000000
8
91
10
11
12
13
Formal Director's Update
Cell Formulas
RangeFormula
E4:E7E4=C4+D4

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### sijpie

##### Well-known Member
If the formulas used to work OK and now have stopped working, this is surely because of the additional hidden corrupted stuff that has bloated the workbook to 45 MB.

To get that corrected is a bit of work.
Make a new workbook
Set up a sheet with the same formatting as this current sheet. Don't copy this sheet across!!

Now take one cell and rebuild the formula in here (you can copy the formula from the old workbook to the new. Don't copy the cell)
Check that it works. Then rebuild the other cells. By modifying your absolute references to semi absolute you will be able to copy down or across a lot of the formulas.

Replies
1
Views
111
Replies
0
Views
41
Replies
13
Views
154
Replies
5
Views
218
Replies
2
Views
97

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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